cross tables, SELECT expressions, and GROUP BY problem - Mailing list pgsql-sql

Hi,

I'm relatively stretched when it comes to SQL but I'm getting there. I
have a question though:

Thanks to various web sites I've succeeded in creating simple cross
tables in PostgreSQL. I'm now trying more complicated examples
(migrating queries from MS Access to PostgreSQL). I'm stuck on getting
grouping to work where the selection element isn't a real table field,
where it is generated by a CASE statement. Here's a full actual SELECT
statement, that works but isn't quite what I need, followed by the first
few rows of output:

SELECT DISTINCTCASE    WHEN lower(c."Order") = 'coleoptera' THEN 5    WHEN lower(c."Order") = 'trichoptera' THEN 8
WHENlower(c."Order") = 'ephemeroptera' THEN 6    WHEN lower(c."Order") = 'plecoptera' THEN 7    WHEN lower(c."Class") =
'oligochaeta'THEN 1    WHEN lower(c."Family") LIKE 'chiron%' THEN 2    WHEN lower(c."Order") = 'diptera' AND
lower(c."Family")NOT LIKE 'chiron%' THEN 3    ELSE 4END AS "Ranking",CASE    WHEN lower(c."Order") = 'coleoptera' THEN
'Coleoptera'   WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'    WHEN lower(c."Order") = 'ephemeroptera' THEN
'Ephemeroptera'   WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'    WHEN lower(c."Class") = 'oligochaeta' THEN
'Oligochaeta'   WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'    WHEN lower(c."Order") = 'diptera' AND
lower(c."Family")NOT LIKE 'chiron%' THEN 'Diptera (Other)'    ELSE 'Other'END AS "Taxa",SUM(CASE WHEN b."LocationCode"
='2222011' THEN c."Count" END) AS "2222011",SUM(CASE WHEN b."LocationCode" = '2222012' THEN c."Count" END) AS
"2222012",SUM(CASEWHEN b."LocationCode" = '2222013' THEN c."Count" END) AS "2222013",SUM(CASE WHEN b."LocationCode" =
'2222014'THEN c."Count" END) AS "2222014"
 
FROM "tblBugIDSheetInfo" bINNER JOIN "tblBugCount" c USING ("BugSheetID")
GROUP BY c."Order", c."Class", c."Family"
ORDER BY "Ranking"

Generates the following output:

Ranking    Taxa            2222011    2222012    2222013    2222014
---------------------------------------------------------------
1    "Oligochaeta"        487    1711    1759    1078
1    "Oligochaeta"        7            
1    "Oligochaeta"                    
2    "Chironomidae"        1385    2335    1500    1513
2    "Chironomidae"                    
3    "Diptera (Other)"                5
3    "Diptera (Other)"        1    1    3
3    "Diptera (Other)"    199    19    40    37
3    "Diptera (Other)"                
...

I want to GROUP on the "Ranking" field as well so that all rows with the
same "Ranking" value are SUMmed. That is, I'm trying to achieve the
following:

Ranking    Taxa            2222011    2222012    2222013    2222014
---------------------------------------------------------------
1    "Oligochaeta"        494    1711    1759    1078
2    "Chironomidae"        1385    2335    1500    1513
3    "Diptera (Other)"    199    20    41    45
...

I tried including "Ranking" at the head of the GROUP BY list, but it has
no effect.

Can anyone show me what I'm doing wrong and/or show me what I need to
change? I'd rather avoid wrapping the whole query in another SELECT that
would duplicate this one, assuming that that would work.

Thanks.
Ross Johnson

PS. I just want to acknowledge the very useful web sites that I found
via google which helped out considerably getting me this far with cross-
tables. The above query is based on this one:

http://dev.mysql.com/tech-resources/articles/wizard/print_version.html