Subject: Re: Help with pivoting tables
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: ben.schneider@comcast.net
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000c01c386c2$ea866700$5011a8c0@dellmobile>
References: <000c01c386c2$ea866700$5011a8c0@dellmobile>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-anRG1gGY2tri1RtYRk8S"
Organization: AK y Asociados S.R.L.
Message-Id: <1064866650.42797.1.camel@taz>
Mime-Version: 1.0
Date: Mon, 29 Sep 2003 17:17:30 -0300

--=-anRG1gGY2tri1RtYRk8S
Content-Type: multipart/alternative; boundary="=-kQk9mJ00gkCHxi+mwqm/"

--=-kQk9mJ00gkCHxi+mwqm/
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

would=20

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,
>=20
> I am having some diffuculty with pivoting the results of a query. I am us=
ing
> the following SQL in an attempt to aggreate the data from a table.
>=20
> ------Begin Query------
>=20
> 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
>=20
> ------End Query------
>=20
> The output is coming back like:
>=20
> 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
>=20
> I need the output to come back with the groups_id and activity_date combi=
ned
> to look like this:
>=20
> 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
>=20
> Any ideas?
>=20
> Thanks,
> Ben
>=20
> ---
> 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=20
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>=20
>                http://www.postgresql.org/docs/faqs/FAQ.html
>=20

--=-kQk9mJ00gkCHxi+mwqm/
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; CHARSET=3DUTF-8">
  <META NAME=3D"GENERATOR" CONTENT=3D"GtkHTML/3.0.8">
</HEAD>
<BODY>
would <BR>
<BR>
SELECT<BR>
	groupid, activity_date,<BR>
	sum(TMP.Attended) AS Attended,<BR>
	sum(TMP.Unexcused) AS Unexcused,<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,<B=
R>
		count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused,<=
BR>
		count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) 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=3DCITE>
<PRE><FONT COLOR=3D"#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,=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 (</FONT><A HREF=3D"http://www.grisoft.com"=
><U>http://www.grisoft.com</U></A><FONT COLOR=3D"#737373">).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003
=20


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               </FONT><A HREF=3D"http://www.postgresql.org/docs/faqs/FAQ.ht=
ml"><U>http://www.postgresql.org/docs/faqs/FAQ.html</U></A>
<FONT COLOR=3D"#737373"></I></FONT></PRE>
</BLOCKQUOTE>
</BODY>
</HTML>

--=-kQk9mJ00gkCHxi+mwqm/--

--=-anRG1gGY2tri1RtYRk8S
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/eJNa21dVnhLsBV0RAnRFAJ9/iE1ic2pAzDsB5LySQ+0GQWqncwCcDaiS
tteGSbKTZSpSKvz7DV33AzI=
=tHVH
-----END PGP SIGNATURE-----

--=-anRG1gGY2tri1RtYRk8S--