From: Ross Johnson <ross.johnson@homemail.com.au>
To: pgsql-sql@postgresql.org
Subject: cross tables, SELECT expressions, and GROUP BY problem
Date: Sun, 02 Apr 2006 16:43:45 +1000
Message-ID: <1143960225.8841.187.camel@desk.home> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ross.johnson@homemail.com.au
Subject: Re: cross tables, SELECT expressions, and GROUP BY problem
In-Reply-To: <1143960225.8841.187.camel@desk.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox