union query question

[johnjohn2]

Using postgresql 7.4

I'm attempting my first union query in my site. Is this even close to being correct syntax?

SELECT Sum(tbl95.col28) AS SumOfcol28, "cleared" AS status
FROM tbl95
GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
HAVING ((tbl95.col2=8478) AND (tbl95.col17=0) AND (tbl95.col39 Is Not Null))


UNION ALL SELECT Sum(tbl95.col28) AS SumOfcol28, "voided" AS status
FROM tbl95
GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
HAVING ((tbl95.col2=8478) AND (tbl95.col17=1))


UNION ALL SELECT Sum(tbl95.col28) AS SumOfcol28, "outstanding" AS status
FROM tbl95
GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
HAVING ((tbl95.col2=8478) AND (tbl95.col17=0) AND (tbl95.col39 Is Null));

[r937]

two problems come to mind

first, when you say

... GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39

this will result in one row per every distinct set of values

for example,

col2 col13 col17 col39
 1     A    34    101  
 1     A    34    102  
 1     A    34    103  
 1     A    37    101  
 1     A    37    102
 1     A    37    103  
 1     B    51    101  
 1     B    52    101         
 2     A    34    101          
 2     A    34    102

now, this in itself isn't bad, but none of these columns are in the SELECT list!!

therefore, the results for the above GROUP BY might be --

SumOfcol28
937
45
62
45
9
234
101
55
36
76

which, as far as i can tell, isn't going to be all that useful

the second problem isn't actually a problem, it'll work, but it's not efficient

all your HAVING conditions should actually be in the WHERE clause, because they are conditions on columns that are actually used in the GROUP BY

which brings us back the the GROUP BY

if you are going to require that a certain column have only a specific value, then there's no point in including it in the GROUP BY, since it will have only one value in any group

this would reduce the GROUP BY to only col13

however, the first point still stands, for each distinct value of col13 you will get a sum, but no way on knowing which sum goes with which value of col13

helps?