From: | <pgsql(at)toddb(dot)mailshell(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Formatting results |
Date: | 2005-09-22 19:27:25 |
Message-ID: | 1127417245.4333059d9ebfd@www.mailshell.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Here is my table:
table tst:
id | date | num
---+---------------------+----
1 | 2005-09-22 14:37:21 | 5
2 | 2005-09-22 10:22:33 | 3
3 | 2005-09-21 10:22:34 | 3
4 | 2005-09-14 10:33:21 | 5
5 | 2005-09-22 17:34:22 | 5
Here is my query:
SELECT num,date_trunc('day',date),COUNT(*) FROM tst
WHERE date_trunc('day',date)<'2005-09-23' AND date_trunc('day',date)>'2005-09-13' GROUP BY num,date_trunc('day',date)
I realize that my date ranges use the entire table in this example. In real life my table has data outside the selected range.
Here are my results:
num| date_trunc | count
---+---------------------+----
3 | 2005-09-21 00:00:00 | 1
3 | 2005-09-22 00:00:00 | 1
5 | 2005-09-14 00:00:00 | 1
5 | 2005-09-22 00:00:00 | 2
Which is what I would expect. What I would like is the output formatted like this:
num| 2005-09-14 | 2005-09-21 | 2005-09-22
---+------------+------------+-----------
3 | 0 | 1 | 1
5 | 1 | 0 | 2
Is there a SQL way to do this? I'd rather not use a stored procedure, and I would like to be able to do this on databases besides postgres. (I am current using postgres, so that is my primary focus. I just like to be general if possible)
Thanks.
Todd
From | Date | Subject | |
---|---|---|---|
Next Message | Andrej Ricnik-Bay | 2005-09-22 19:40:23 | Re: Import dbf data |
Previous Message | Rafael Barbosa | 2005-09-22 19:22:37 | Import dbf data |