cross tables, SELECT expressions, and GROUP BY problem

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 DISTINCT
CASE
WHEN lower(c."Order") = 'coleoptera' THEN 5
WHEN lower(c."Order") = 'trichoptera' THEN 8
WHEN lower(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 4
END 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(CASE WHEN b."LocationCode" = '2222013' THEN c."Count" END) AS "2222013",
SUM(CASE WHEN b."LocationCode" = '2222014' THEN c."Count" END) AS "2222014"
FROM "tblBugIDSheetInfo" b
INNER 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