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

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°