Reply-To: <ben.schneider@comcast.net>
From: "Ben Schneider" <ben.schneider@comcast.net>
To: <pgsql-sql@postgresql.org>
Subject: Help with pivoting tables
Date: Mon, 29 Sep 2003 12:50:20 -0700
Message-ID: <000c01c386c2$ea866700$5011a8c0@dellmobile>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Importance: Normal

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,=20
count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended,=20
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
=20