[SQL] combine SQL SELECT statements into one
Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified - -- I778288176 2010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM765 2010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx> :) NEiL
Re: [SQL] [NOVICE] combine SQL SELECT statements into one
In response to Neil Stlyz : > Good Evening, Good Morning Wherever you are whenever you may be reading this. > > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > > > I am currently working on a PHP / PostgreSQL project and I came upon something > I could not figure out in SQL. I was wondering if anyone here could take a > look > and perhaps offer some guidance or assistance in helping me write this SQL > query. > > Please Consider the following information: > --- > > I have a postgresql table called 'inventory' that includes two fields: 'model' > which is a character varying field and 'modified' which is a timestamp field. > > So the table inventory looks something like this: > > > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > > > > Now with the table, fields and data in mind look at the following three > queries: > > > > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; > > > > All three of the above queries work and provide results. However, I want to > combine the three into one SQL Statement that hits the database one time. How > can I do this in one SQL Statement? Is it possible with sub select? > > > > Here is what result I am looking for from one SELECT statement using the data > example from above: > > > > count1 | count2 | count3 > --- > 2 2 4 > > > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? test=# select * from inventory ; model| modified +- I778288176 | 2010-02-01 08:27:00 I778288176 | 2010-01-31 11:23:00 I778288176 | 2010-01-29 10:46:00 JKLM112345 | 2010-02-01 08:25:00 JKLM112345 | 2010-01-31 09:52:00 JKLM112345 | 2010-01-28 09:44:00 X22TUNM765 | 2010-01-17 10:13:00 V8893456T6 | 2010-01-01 09:17:00 (8 rows) test=*# select count(distinct count1), count(distinct count2), count(distinct count3) from (select distinct case when modified >= '2010-02-01' then model else null end as count1, case when modified >= '2010-01-20' then model else null end as count2, case when modified >= '2010-01-01' then model else null end as count3 from inventory) foo ; count | count | count ---+---+--- 2 | 2 | 4 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evening, Good Morning Wherever you are whenever you may be reading this. > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone here could > take a look and perhaps offer some guidance or assistance in helping me write > this SQL query. > Please Consider the following information: > --- > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > So the table inventory looks something like this: > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > Now with the table, fields and data in mind look at the following three > queries: > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; > All three of the above queries work and provide results. However, I want to > combine the three into one SQL Statement that hits the database one time. How > can I do this in one SQL Statement? Is it possible with sub select? > Here is what result I am looking for from one SELECT statement using the data > example from above: > count1 | count2 | count3 > --- > 2 2 4 > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? > Please let me know. > Thanx> :) > NEiL > Здесь спама нет http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evening, Good Morning Wherever you are whenever you may be reading this. > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone here could > take a look and perhaps offer some guidance or assistance in helping me write > this SQL query. > Please Consider the following information: > --- > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > So the table inventory looks something like this: > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > Now with the table, fields and data in mind look at the following three > queries: > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; > All three of the above queries work and provide results. However, I want to > combine the three into one SQL Statement that hits the database one time. How > can I do this in one SQL Statement? Is it possible with sub select? > Here is what result I am looking for from one SELECT statement using the data > example from above: > count1 | count2 | count3 > --- > 2 2 4 > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? > Please let me know. > Thanx> :) > NEiL > Здесь спама нет http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] combine SQL SELECT statements into one
msi77 wrote: > Hi, > > SELECT > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') > as count1, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') > as count2, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') > as count3 But this statement will seq scan the table inventory three times as an explain analyze easily will show, while the solution from Andreas will do only one seq scan. This can be a big difference, depending on the size of the table. > > Serge > >> Good Evening, Good Morning Wherever you are whenever you may be reading >> this. >> I am new to this email group and have some good experience with SQL and >> PostgreSQL database. >> I am currently working on a PHP / PostgreSQL project and I came upon >> something I could not figure out in SQL. I was wondering if anyone here >> could take a look and perhaps offer some guidance or assistance in helping >> me write this SQL query. >> Please Consider the following information: >> --- >> I have a postgresql table called 'inventory' that includes two fields: >> 'model' which is a character varying field and 'modified' which is a >> timestamp field. >> So the table inventory looks something like this: >> model modified >> --- >> I7782881762010-02-01 08:27:00 >> I778288176 2010-01-31 11:23:00 >> I778288176 2010-01-29 10:46:00 >> JKLM112345 2010-02-01 08:25:00 >> JKLM112345 2010-01-31 09:52:00 >> JKLM112345 2010-01-28 09:44:00 >> X22TUNM7652010-01-17 10:13:00 >> V8893456T6 2010-01-01 09:17:00 >> Now with the table, fields and data in mind look at the following three >> queries: >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; >> All three of the above queries work and provide results. However, I want to >> combine the three into one SQL Statement that hits the database one time. >> How can I do this in one SQL Statement? Is it possible with sub select? >> Here is what result I am looking for from one SELECT statement using the >> data example from above: >> count1 | count2 | count3 >> --- >> 2 2 4 >> Can this be done with ONE SQL STATEMENT? touching the database only ONE >> time? >> Please let me know. >> Thanx> :) >> NEiL >> > > Здесь спама нет http://mail.yandex.ru/nospam/sign > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] selecting rows tagged with "a" but not "b"
Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? thanks Darrell -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
[email protected] написа: Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? Replace "SUBSTRACT" with "EXCEPT" (http://www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT). -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
Darrell, Can you provide a little more information and background on your problem. please? What values can the "tag" column assume? Just "a" and "b" ? Both? Please give examples of table contents and desired output, your mail doesn't contain enough info to give you more advises Thank you Best, Oliveiros - Original Message - From: <[email protected]> To: Sent: Monday, February 01, 2010 1:31 PM Subject: [SQL] selecting rows tagged with "a" but not "b" Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? thanks Darrell -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
Hi Darrell, SELECT DISTINCT articleID FROM tags WHERE tag = "a" EXCEPT SELECT DISTINCT articleID FROM tags WHERE tag = "b"; Regards, Andreas -Ursprüngliche Nachricht- Von: [email protected] [mailto:[email protected]] Im Auftrag von [email protected] Gesendet: Montag, 1. Februar 2010 14:32 An: [email protected] Betreff: [SQL] selecting rows tagged with "a" but not "b" Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? thanks Darrell -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
[email protected] <[email protected]> wrote: > Hi, > > I have a two tables: > > article > articleID, name, content > > tags > articleID, tag > > I want to find all articles that are tagged with "a" but not "b" > > how do I do this? select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; 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-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
Andreas Kretschmer wrote: [email protected] <[email protected]> wrote: Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; select a.* from article left join tags t on a.articleID=t.articleID where t.tag = 'a' where not exists (select * from tags t2 where t2.articleID=a.articleID and t2.tag = 'b'); Yeb -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Crosstab Confusion
I'm really trying to understand how the tablefunc crosstab function works, to no avail. I have a table that looks like this: customer_id integer date timestamp with time zone amount numeric(10,4) There are rows in this table every-time a customer gets charged an amount, which is multiple times per day. I would like to get a result like this: customer_id,day1,day2,day3,(…) 1,400.00,500.01,123.00,(…) So, one row for each customer id and a column for every day in the current month. Anyone used crosstab for something like this? Thanks for your help, A. -- www.sherman.ca / +1-613-797-6819 / +1-646-233-3400 "When the burning husks of your startups warm the last of your bones, remember I told you so." - Zed -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Crosstab Confusion
I'm flying blind here since I have nothing that looks like the structure you
described to experiment on, but based on some crosstabs I have set up this
should get you started. Explanatory notes follow.
SELECT pivot.*
FROM crosstab('
--row header, column header, cell value
SELECT customer_id, extract(day FROM date), sum(amount)
WHERE extract(month FROM date) = 1 --desired month, 1=Jan, 2=Feb, etc.
FROM your_table
GROUP BY 1, 2
ORDER BY 1, 2
','
--list of column headers
SELECT 1, 2, 3, […] 31
')
--list of column names for final result set
--does not have to match column names from crosstab() section!
pivot(customer_id integer, day1 numeric(10,4), day2 numeric(10,4), day3
numeric(10,4), […] day31 numeric(10,4))
;
You basically have three parts:
1) SELECT query in the form (row header, column header, cell value). In this
case it is an aggregate query so that you can sum the transactions over a
given day.
2) List of column headers. If you want, this can SELECT from another table,
so you can have a table with rows 1, 2, 3, etc and use it to select the days
from the month instead of listing them manually.
3) List of output columns, which follows "pivot" in the text above. Note
that "pivot" is an arbitrary name. You can use foo, bar, or whatever, but
that will be the name of the table which must be used to reference the
columns in the top SELECT list.
Note that my somewhat bizarre indenting / end of line structure is designed
so that, when using pgAdmin, I can very quickly select and execute just the
value query or column header query, which lets me experiment as I build the
crosstab.
Note, finally, that the list of output columns (following "pivot") should be
one more than the number of columns in section 2 (technically, you can
circumvent this limitation, but it's complicated and seems unnecessary for
your use case). That could be problematic when running this query in the
middle of the month and the number of value rows per customer doesn't match
the number expected in the list of column headers and list of output
columns. I don't know of any way to make the list of output columns vary
dynamically, but perhaps someone else here does? Otherwise, you would have
to either (a) make 31 different crosstab queries and choose which one to run
based on the day of the month, or (b) come up with some way to "pad" the
value table created in part 1 so that it lists customer_id, day, 0 for all
days which have no customer transactions. (Perhaps create a dummy
customer_id that has no transactions for all days in the month, which should
be enough to trick the crosstab function into thinking it has something to
fill the last columns.
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
Re: [SQL] Crosstab Confusion
On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:
> You basically have three parts:
>
> 1) SELECT query in the form (row header, column header, cell value). In this
> case it is an aggregate query so that you can sum the transactions over a
> given day.
>
> 2) List of column headers. If you want, this can SELECT from another table,
> so you can have a table with rows 1, 2, 3, etc and use it to select the days
> from the month instead of listing them manually.
>
> 3) List of output columns, which follows "pivot" in the text above. Note that
> "pivot" is an arbitrary name. You can use foo, bar, or whatever, but that
> will be the name of the table which must be used to reference the columns in
> the top SELECT list.
Wow that's an incredibly complete response!
I'm not getting any data in my rows though. This query produces the data:
SELECT
cust_id as customer,
date_trunc('day', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >= '2010-01-01'
GROUP BY 1,2
ORDER BY 1,2;
Which looks like:
customer | day | minutes
--+-+-
1 | 2010-01-01 00:00:00 |1110
1 | 2010-01-03 00:00:00 | 60
1 | 2010-01-26 00:00:00 | 23010
1 | 2010-01-27 00:00:00 | 17910
2 | 2010-01-01 00:00:00 | 60
2 | 2010-01-02 00:00:00 | 30
2 | 2010-01-04 00:00:00 | 26310
etc, etc, etc
But this query:
-- clients by day
SELECT pivot.* FROM crosstab(
'SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >= ''2010-01-01''
GROUP BY 1,2
ORDER BY 1,2',
'select * from day_of_month'
) pivot (
customer integer,
day1 numeric(10,4),
day2 numeric(10,4),
(…)
day31 numeric(10,4)
)
ORDER BY customer;
Gives me a table that looks right but all values are null for the days.
Something simple maybe?
Thanks,
A.
--
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
"When the burning husks of your startups warm the last of your bones, remember
I told you so." - Zed
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Crosstab Confusion
The output column data type (day1, day2, etc.) is supposed to match the
value data type. I used numeric(10,4) because that's what your original post
specified, but the billed_duration column in your most recent post looks
like it might be integer? (Or is it defined as numeric(10,4), but you never
enter noninteger values?)
What's the output of the category query by itself? I forgot to include ORDER
BY 1 at the end of the category query. (The order should match the order of
output columns, but I think without it you wouldn't get NULL values, just
the values would be in the wrong columns.) I assume day_of_month has only
one column, but I would suggest naming it explicitly instead of using *. And
is the day_of_month column defined in the same format as date_trunc('day',
date)? They must successfully pass an "equals" test to get included in the
right crosstab cell. If a category value in the source query doesn't match
any value produced by the category query, I think the crosstab function just
throws out that row, which could lead to a table with the correct structure
but all NULLs.
I didn't think about this before, but you can also pull the category headers
from your source table like this:
'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >=
''2010-01-01'' ORDER BY 1'
Let me know if this gets you anywhere.
--Lee
On Mon, Feb 1, 2010 at 11:52 AM, Adam Sherman wrote:
> On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:
> > You basically have three parts:
> >
> > 1) SELECT query in the form (row header, column header, cell value). In
> this case it is an aggregate query so that you can sum the transactions
> over a given day.
> >
> > 2) List of column headers. If you want, this can SELECT from another
> table, so you can have a table with rows 1, 2, 3, etc and use it to select
> the days from the month instead of listing them manually.
> >
> > 3) List of output columns, which follows "pivot" in the text above. Note
> that "pivot" is an arbitrary name. You can use foo, bar, or whatever, but
> that will be the name of the table which must be used to reference the
> columns in the top SELECT list.
>
> Wow that's an incredibly complete response!
>
> I'm not getting any data in my rows though. This query produces the data:
>
> SELECT
> cust_id as customer,
> date_trunc('day', date) AS day,
> SUM(billed_duration) AS minutes
> FROM master_cdr
> WHERE date >= '2010-01-01'
> GROUP BY 1,2
> ORDER BY 1,2;
>
> Which looks like:
>
> customer | day | minutes
> --+-+-
>1 | 2010-01-01 00:00:00 |1110
>1 | 2010-01-03 00:00:00 | 60
>1 | 2010-01-26 00:00:00 | 23010
>1 | 2010-01-27 00:00:00 | 17910
>2 | 2010-01-01 00:00:00 | 60
>2 | 2010-01-02 00:00:00 | 30
>2 | 2010-01-04 00:00:00 | 26310
> etc, etc, etc
>
> But this query:
>
> -- clients by day
> SELECT pivot.* FROM crosstab(
> 'SELECT
>cust_id as customer,
>date_trunc(''day'', date) AS day,
>SUM(billed_duration) AS minutes
> FROM master_cdr
> WHERE date >= ''2010-01-01''
> GROUP BY 1,2
> ORDER BY 1,2',
> 'select * from day_of_month'
> ) pivot (
> customer integer,
> day1 numeric(10,4),
> day2 numeric(10,4),
> (…)
> day31 numeric(10,4)
> )
> ORDER BY customer;
>
> Gives me a table that looks right but all values are null for the days.
>
> Something simple maybe?
>
> Thanks,
>
> A.
>
> --
> www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
>
> "When the burning husks of your startups warm the last of your bones,
> remember I told you so." - Zed
>
>
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
Re: [SQL] Crosstab Confusion
On 2010-02-01, at 14:22 , Lee Hachadoorian wrote:
> The output column data type (day1, day2, etc.) is supposed to match the value
> data type. I used numeric(10,4) because that's what your original post
> specified, but the billed_duration column in your most recent post looks like
> it might be integer? (Or is it defined as numeric(10,4), but you never enter
> noninteger values?)
Actually, the query I was running is:
SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration)/60.0::numeric(10,4) AS minutes
billed_duration is an integer. Make sense?
> What's the output of the category query by itself? I forgot to include ORDER
> BY 1 at the end of the category query. (The order should match the order of
> output columns, but I think without it you wouldn't get NULL values, just the
> values would be in the wrong columns.) I assume day_of_month has only one
> column, but I would suggest naming it explicitly instead of using *. And is
> the day_of_month column defined in the same format as date_trunc('day',
> date)? They must successfully pass an "equals" test to get included in the
> right crosstab cell. If a category value in the source query doesn't match
> any value produced by the category query, I think the crosstab function just
> throws out that row, which could lead to a table with the correct structure
> but all NULLs.
Right, my list of columns weren't equal to the truncated date. Using your
suggested query to generate the columns fixed the problem!
Now, is there a way to generate the labels? Otherwise I have to adjust the
query for th number of days returned.
Such nice output though! Awesome!
Thanks,
A.
--
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
"When the burning husks of your startups warm the last of your bones, remember
I told you so." - Zed
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Crosstab Confusion
On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman wrote:
> Actually, the query I was running is:
>
> SELECT
>cust_id as customer,
>date_trunc(''day'', date) AS day,
> SUM(billed_duration)/60.0::numeric(10,4) AS minutes
>
> billed_duration is an integer. Make sense?
>
> If billed_duration is an integer, sum(billed_duration) will be int or
bigint. I would just define the output columns as bigint (day1 bigint, day2
bigint, etc.). Although, formatting it in the source SQL (I see you are
dividing by 60 and casting to numeric) saves you from having to format 31
output columns.
Right, my list of columns weren't equal to the truncated date. Using your
> suggested query to generate the columns fixed the problem!
>
> Now, is there a way to generate the labels? Otherwise I have to adjust the
> query for th number of days returned.
>
>
Not that I know of, but I have confirmed that as long as the category SQL
matches the output column list, you can have output columns with no data in
them. In order to have your list of column headers match the source SQL, I
would recommend going back to the extract() function I first recommended to
extract the day of month as an integer, and then generate a 31 number series
for your category headers. It would look like this:
SELECT pivot.* FROM crosstab(
'SELECT
cust_id as customer,
extract(day from date)::integer AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE extract(month FROM date) = 1
GROUP BY 1,2
ORDER BY 1,2',
'select day from generate_series(1,31) day'
) pivot (
customer integer,
day1 bigint,
day2 bigint,
(…)
day31 bigint
)
ORDER BY customer;
For half-over months or months with fewer than 31 days, the final columns of
the crosstab should just be blank.
PS: The way I have constructed it, I would avoid using WHERE date >=
''2010-01-01''. If data from February gets into the table, it will aggregate
data from, e.g. Jan 2 and Feb 2 as both being part of "Day 2".
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
