Re: multiple rows by date using count(*)

Shad Keene <shadkeene(at)hotmail(dot)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 value else 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 else 0 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°