Shad Keene <shadkeene@xxxxxxxxxxx> 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° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
