Re: cross tables, SELECT expressions, and GROUP BY problem

On Sun, 2006-04-02 at 16:43 +1000, Ross Johnson wrote:
> 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 should add that I've just tried the following query, which is just a
rearrangement of the above query using a sub-SELECT, and the result is
the same as above apart from a slightly different ordering of the rows.
That is, GROUP BY "Ranking" still doesn't appear to do anything. I was
under the impression that a sub-SELECT creates a temporary, or at least
a pseudo-temporary table (a "virtual" table as it's called in the
documentation), so I would have expected this query to work even if the
above one doesn't.

SELECT DISTINCT
"Ranking", "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 (
SELECT "BugSheetID",
CASE
WHEN lower("Order") = 'coleoptera' THEN 5
WHEN lower("Order") = 'trichoptera' THEN 8
WHEN lower("Order") = 'ephemeroptera' THEN 6
WHEN lower("Order") = 'plecoptera' THEN 7
WHEN lower("Class") = 'oligochaeta' THEN 1
WHEN lower("Family") LIKE 'chiron%' THEN 2
WHEN lower("Order") = 'diptera' AND lower("Family") NOT LIKE 'chiron%' THEN 3
ELSE 4
END AS "Ranking",
CASE
WHEN lower("Order") = 'coleoptera' THEN 'Coleoptera'
WHEN lower("Order") = 'trichoptera' THEN 'Trichoptera'
WHEN lower("Order") = 'ephemeroptera' THEN 'Ephemeroptera'
WHEN lower("Order") = 'plecoptera' THEN 'Plecoptera'
WHEN lower("Class") = 'oligochaeta' THEN 'Oligochaeta'
WHEN lower("Family") LIKE 'chiron%' THEN 'Chironomidae'
WHEN lower("Order") = 'diptera' AND lower("Family") NOT LIKE 'chiron%' THEN 'Diptera (Other)'
ELSE 'Other'
END AS "Taxa",
"Order", "Class", "Family", "Count"
FROM "tblBugCount") c USING ("BugSheetID")
GROUP BY c."Ranking", c."Taxa", c."Order", c."Class", c."Family"
ORDER BY "Ranking"

In the doco, I found this statement:
"In strict SQL, GROUP BY can only group by columns of the source table
but PostgreSQL extends this to also allow GROUP BY to group by columns
in the select list. Grouping by value expressions instead of simple
column names is also allowed."

This seems to relate to what I'm trying to do, and seems to say that it
should be possible, unless I'm misreading it.

Is my only option to create an actual temporary table? I was hoping to
avoid that. If I must, is there a function to generate unique table
names that I can use?

Regards.
Ross Johnson

> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>