Thread: multiple rows by date using count(*)

Hi,
I have data that I'd like to organize spatially by date.  So far, I'm only succeeding in displaying the number of times something occurs out of the whole dataset.

Here's an example of what I want to do.
343 items that occurred at different times from june through july.
multiple different keywords that are within the items

I want to organize the data into items that occurred in the month of june with dates as the columns (so 30 columns) and keywords that occur in the items as the row...to look like this:


            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;

Further down the road I want to do spatial relationships linking to a different georeferenced table, but I think if I can get the above example working, the joining shouldn't be overly difficult.

Thanks for your time and let me know if you need more details...the computer I'm doing the queries on is a different one than I'm typing this email, so thus the crude examples.
Shad

---

Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on your BlackBerry or iPhone.

Shad Keene <shadkeene@hotmail.com> wrote:

> Hi,
> I have data that I'd like to organize spatially by date.  So far, I'm only
> succeeding in displaying the number of times something occurs out of the whole
> dataset.
>
> Here's an example of what I want to do.
> 343 items that occurred at different times from june through july.
> multiple different keywords that are within the items
>
> I want to organize the data into items that occurred in the month of june with
> dates as the columns (so 30 columns) and keywords that occur in the items as
> the row...to look like this:
>
>
>             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;
>
> Further down the road I want to do spatial relationships linking to a different
> georeferenced table, but I think if I can get the above example working, the
> joining shouldn't be overly difficult.
>
> Thanks for your time and let me know if you need more details...the computer
> I'm doing the queries on is a different one than I'm typing this email, so thus
> the crude examples.
> Shad

You can use the contrib-module called 'tablefunc', it contains a
crosstab() - funktion.

An other easy way, let me show an example:

test=*# select * from foo;
   datum    | category | value
------------+----------+-------
 2009-07-01 | foo      |    10
 2009-07-01 | bla      |    20
 2009-07-01 | bla      |     5
 2009-07-01 | foo      |    14
 2009-07-02 | foo      |    22
(5 rows)

Time: 0.221 ms
test=*# select datum, sum(case when category='foo' then value else 0 end) as "foo", sum(case when category='bla' then
valueelse 0 end) as "bla" from foo group by datum order by datum; 
   datum    | foo | bla
------------+-----+-----
 2009-07-01 |  24 |  25
 2009-07-02 |  22 |   0
(2 rows)

Time: 0.353 ms
test=*# select datum, sum(case when category='foo' then 1 else 0 end) as "foo", sum(case when category='bla' then 1
else0 end) as "bla" from foo group by datum order bydatum; 
   datum    | foo | bla
------------+-----+-----
 2009-07-01 |   2 |   2
 2009-07-02 |   1 |   0
(2 rows)





Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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!