Re: Help with pivoting tables - Mailing list pgsql-sql

would <br /><br /> SELECT<br /> groupid, activity_date,<br /> sum(TMP.Attended) AS Attended,<br /> sum(TMP.Unexcused)
ASUnexcused,<br /> sum(TMP.Absent) AS Absent,<br /> sum(TMP.Called) AS Called<br /> FROM (<br /> SELECT groupid,
activity_date,<br/> count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended,<br /> count(CASE
activity_subcodeWHEN 101 THEN 1 ELSE NULL END) AS Unexcused,<br /> count(CASE activity_subcode WHEN 102 THEN 1 ELSE
NULLEND) AS Absent,<br /> count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called,<br /> count(*) AS
total<br/> FROM activity<br /> WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003'<br /> GROUP BY groupid,
activity_date,activity_subcode<br /> ORDER BY groupid, activity_date<br /> ) TMP<br /> GROUP BY groupid,
activity_date<br/> ORDER BY groupid, activity_date<br /><br /> do what you want?<br /><br /> On Mon, 2003-09-29 at
16:50,Ben Schneider wrote: <blockquote type="CITE"><pre><font color="#737373"><i>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 (</i></font><a href="http://www.grisoft.com"><u>http://www.grisoft.com</u></a><font
color="#737373">).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              </font><a
href="http://www.postgresql.org/docs/faqs/FAQ.html"><u>http://www.postgresql.org/docs/faqs/FAQ.html</u></a>
<font color="#737373"></font></pre></blockquote>