Re: multiple rows by date using count(*) - Mailing list pgsql-general

On Jul 5, 2009, at 5:20 AM, Shad Keene wrote:

>             PRB | RBL | SAC | SFO |
> June 1     2   |  4   |  5    |  2    |
> June 2     1   |  3   |  4    |  0    |
> June 3     0   |  2   |  1    |  2    |
>
> So far, here's the query I'm using to display one row of all items
> with certain keywords, but I've failed at trying to make multiple
> rows by date.
>
> Here's the query I'm using so far:
> select (select count (*) from zoa_pireps where raw_text like '%RBL
> %') as RBL, (select count(*) from zoa_pireps where raw_text like
> '%RBL%') as PRB;


I think you're looking for something like this:

select date, sum(case when raw_text like '%RBL%' then 1 else 0 end) as
RBL, sum(case when raw_text like '%PRB%' then 1 else 0 end) as PRB
from zoa_pireps group by date.

It's probably a lot more readable if you wrap those expressions in an
immutable function.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a5071bf759151100320669!