pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with pivoting tables
2+ messages / 2 participants
[nested] [flat]
* Help with pivoting tables
@ 2003-09-29 19:50 Ben Schneider <ben.schneider@comcast.net>
0 siblings, 1 reply; 2+ messages in thread
From: Ben Schneider @ 2003-09-29 19:50 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with pivoting tables
@ 2003-09-29 20:17 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Ben Schneider <ben.schneider@comcast.net>
0 siblings, 0 replies; 2+ messages in thread
From: Franco Bruno Borghesi @ 2003-09-29 20:17 UTC (permalink / raw)
To: ben.schneider@comcast.net; +Cc: pgsql-sql
would
SELECT
groupid, activity_date,
sum(TMP.Attended) AS Attended,
sum(TMP.Unexcused) AS Unexcused,
sum(TMP.Absent) AS Absent,
sum(TMP.Called) AS Called
FROM (
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
) TMP
GROUP BY groupid, activity_date
ORDER BY groupid, activity_date
do what you want?
On Mon, 2003-09-29 at 16:50, Ben Schneider wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-09-29 20:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 19:50 Help with pivoting tables Ben Schneider <ben.schneider@comcast.net>
2003-09-29 20:17 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
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