Help with pivoting tables
От | Ben Schneider |
---|---|
Тема | Help with pivoting tables |
Дата | |
Msg-id | 000c01c386c2$ea866700$5011a8c0@dellmobile обсуждение исходный текст |
Ответы |
Re: Help with pivoting tables
|
Список | 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
В списке pgsql-sql по дате отправления: