Re: multiple rows by date using count(*)

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!