multiple rows by date using count(*)

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 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�

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!