Re: Pulling my hair out with this query

[Chris Stubben]

Hi,

I bet sum(CASE) statements will be faster than subselects, and cross-joins will be the fastest solution. You can try testing the cross-join query below..

create table test (name char(4), year smallint, test1 smallint);

insert into test values ('John', 2002, 80)
,('Anna', 2002, 67)
,('John', 2003, 78)
,('Bill', 2003, 76)
,('Anna', 2003, 77)
,('John', 2004, 88)
,('Bill', 2004, 77)
,('Phil', 2004, 76);


CREATE table crosstabs (year smallint, year1 tinyint, year2 tinyint,year3 tinyint);
INSERT into crosstabs values (2002, 1,0,0);
INSERT into crosstabs values (2003, 0,1,0);
INSERT into crosstabs values (2004, 0,0,1);

SELECT t.name, sum(t.test1*c.year1) as total2002,
sum(t.test1*c.year2) as total2003,
sum(t.test1*c.year3) as total2004
FROM test t, crosstabs c
WHERE t.year=c.year GROUP by t.name;

+------+-----------+-----------+-----------+
| name | total2002 | total2003 | total2004 |
+------+-----------+-----------+-----------+
| Anna | 67 | 77 | 0 |
| Bill | 0 | 76 | 77 |
| John | 80 | 78 | 88 |
| Phil | 0 | 0 | 76 |
+------+-----------+-----------+-----------+
--4 rows in set (0.00 sec)

--If you want to see nulls instead of zeros for test scores use nullif

SELECT t.name,
nullif(sum(t.test1*c.year1),0) as total2002,
nullif(sum(t.test1*c.year2),0) as total2003,
nullif(sum(t.test1*c.year3),0) as total2004
FROM test t, crosstabs c
WHERE t.year=c.year GROUP by t.name;



Chris