Posted by MGFoster on 05/27/05 23:35

rong.guo@gmail.com wrote:
> Hello group!
>
> I would like to make a sort of pivot table using the raw data below,
> but my query wouldn't give me results in the ideal output format
> (please see below). Any idea how to deal with it? Thanks a million!
>
> create table a
> (referid varchar(255)
> ,app_id int
> ,description varchar (255)
> ,time datetime
> ,amount money)
>
> insert into a values('A111',1111, 'Checking', '3/25/2005',6000)
> insert into a values('A111',1112, 'Savings', '4/15/2005',3000)
> insert into a values('A111',1113, 'Checking', '3/12/2005',5000)
> insert into a values('A111',1114, 'Savings', '4/2/2005',8000)
> insert into a values('A111',1115, 'Checking', '4/25/2005',2000)
> insert into a values('A111',1116, 'Savings', '3/25/2005',3000)
> insert into a values('A111',1117, 'Checking', '4/2/2005',5000)
> insert into a values('A111',1118, 'Savings', '3/12/2005',10000)
>
> --Ideal Output--
> referid description march_apps march_amt april_apps april_amt
> A111 Checking 2 9000 2 13000
> A111 Savings 2 15000 2 5000
>
> --My Query--
> select referid, description
> ,march_apps=case
> when year(time)=2005 and month(time)=3
> then count(app_id) end
> ,april_apps=case
> when year(time)=2005 and month(time)=4
> then count(app_id) end
>>From a
> Group by referid, description, time
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your CASE expressions should be like this:

,march_apps = COUNT(CASE WHEN Year(time)=2005 AND Month(time)=3
THEN app_id ELSE NULL END)
,april_apps = COUNT(CASE WHEN Year(time)=2005 AND Month(time)=4
THEN app_id) ELSE NULL END)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpeEfIechKqOuFEgEQK/UACg6KTx0s1buMshqn2pPecgCoVo/AQAoJkB
frb/qM3Q2KCqoqsMlP/k59gO
=rsrt
-----END PGP SIGNATURE-----

[Back to original message]