I have a table with a structure such as table(PK, a, b, c, d, e, f, g).

And 开发者_开发百科I have many queries that I want to join:

select PK, sum(c) where...

JOIN

select PK, sum(e) where...

JOIN

select PK, sum(g) where ...

JOIN

 select PK,a,b,d,f

Every sum(c|e|g) is actually a

select sum(c|e|g) from .... where...

because there are many conditions involved (not all c|e|g's must be added)

Is this the best way to do it? I was suggested to write it in PL / SQL, which I would have to learn. If its the way to go, i'll do it, but im not sure whats wrong with the solution shown above.

Edit

Im pretty sure its a Join. Here's what I want.

I need to get a result set in the form:

PK, a,b,COMPLEX_SUM_ON_C,d,COMPLEX_SUM_ON_D,f,COMPLEX_DUM_ON_G

so I thought of joining many queries to get this result.

Each of the COMPLEX... is another select (select sum...). This is a very big table, and writing

select a,b,(select sum..),d,(select sum...),f,(select sum...)

will yield bad performance (so I was told to remove it)

I've edited my query above.

Thanks in advance.

---

I think you mean "UNION" not "JOIN". Whether is the best way depends on what you're trying to achieve.

---

This is not a well-defined problem (yet).

Assuming PK is your primary key (i.e. unique, by definition), then

SELECT PK, SUM(c)
FROM tbl
GROUP BY PK

is ALWAYS the same as

SELECT PK, c
FROM tbl

So grouping (and aggregating) is relatively meaningless.

In your expected results:

PK, a,b,COMPLEX_SUM_ON_C,d,COMPLEX_SUM_ON_D,f,COMPLEX_DUM_ON_G

How are COMPLEX_SUM_ON_C, COMPLEX_SUM_ON_D, COMPLEX_DUM_ON_G related to PK?

We know how a, b, d, f are related to PK, because for each PK, one can identify the one and only a, b, d, f on the same row.

---

An example of a JOIN is the following:

Select a.col1, b.col2
FROM table1 a, table2 b
WHERE a.key = b.key;

which can also be written:

SELECT a.col1, b.col2
FROM table1 a
INNER JOIN table2 b
ON a.key = b.key;

Edit:

After reading your re-edit of the original question, you can probably use a JOIN. JOINs can be used when you have related data in more than one table, or you can specifiy the same table multiple times. I have used both kinds with Oracle. Here's an example of the latter kind which will hopefully help you:

SELECT t1.a, t1.b, t3.sum(c), t2.d, t4.sum(e), t1.f, t5.sum(g)
FROM table1 t1, table1 t2, table1 t3, table1 t4, table1 t5
WHERE  t1.a = 'hello'
AND t2.a = 'world'
AND t3.c = 10
AND t4.e = 20
AND t5.g = 100
GROUP BY t1.a, t1.b, t2.d, t1.f;