From: | "Ben Schneider" <ben(dot)schneider(at)comcast(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Help with pivoting tables |
Date: | 2003-09-29 19:50:20 |
Message-ID: | 000c01c386c2$ea866700$5011a8c0@dellmobile |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I am having some diffuculty with pivoting the results of a query. I am using
the following SQL in an attempt to aggreate the data from a table.
------Begin Query------
SELECT groupid, activity_date,
count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended,
count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused,
count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) AS Absent,
count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called,
count(*) AS total
FROM activity
WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003'
GROUP BY groupid, activity_date, activity_subcode
ORDER BY groupid, activity_date
------End Query------
The output is coming back like:
Groupid activity_date attended unexcused absent called total
---------------------------------------------------------------------
BNEIO 2003-06-04 7 0 0 0 7
BNEIO 2003-06-04 0 2 0 0 2
BNEIO 2003-06-05 4 0 0 0 4
BNEIO 2003-06-05 0 5 0 0 5
I need the output to come back with the groups_id and activity_date combined
to look like this:
Groupid activity_date attended unexcused absent called total
---------------------------------------------------------------------
BNEIO 2003-06-04 7 2 0 0 9
BNEIO 2003-06-05 4 5 0 0 9
Any ideas?
Thanks,
Ben
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003
From | Date | Subject | |
---|---|---|---|
Next Message | Franco Bruno Borghesi | 2003-09-29 20:17:30 | Re: Help with pivoting tables |
Previous Message | scott.marlowe | 2003-09-29 19:33:24 | Re: [SQL] Result set granularity.. |