Thread: how to construct sql
I am grabbing a printer total and putting it in a table. The page_count is continuously increasing: page_count_count page_count_pdate 10 2010-05-10 20 2010-05-10 40 2010-05-11 60 2010-05-11 80 2010-05-11 100 2010-05-12 120 2010-05-12 ..... and so on. I can do: select sum(page_count_count) from page_count group by page_count_pdate. and get a total for a day. But this is not the total I want. I want the total page count for the day. This would mean getting the first page count of the day and then subtracting that from last page_count for the day. For 2010-05-11 above it would be 80 - 40 = 40 total for the day. Is there a way to do this with sql? thx, -wes
Hi, Have you already tried this out? select MAX(page_count_count) - MIN(page_count_count) from page_count group by page_count_pdate. Best, Oliveiros ----- Original Message ----- From: "Wes James" <comptekki@gmail.com> To: <pgsql-sql@postgresql.org> Sent: Wednesday, June 02, 2010 5:48 PM Subject: [SQL] how to construct sql >I am grabbing a printer total and putting it in a table. The > page_count is continuously increasing: > > page_count_count page_count_pdate > 10 2010-05-10 > 20 2010-05-10 > 40 2010-05-11 > 60 2010-05-11 > 80 2010-05-11 > 100 2010-05-12 > 120 2010-05-12 > ..... > > and so on. > > I can do: > > select sum(page_count_count) from page_count group by page_count_pdate. > > and get a total for a day. But this is not the total I want. I want > the total page count for the day. This would mean getting the first > page count of the day and then subtracting that from last page_count > for the day. For 2010-05-11 above it would be > > 80 - 40 = 40 total for the day. Is there a way to do this with sql? > > thx, > > -wes > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros <oliveiros.cristina@marktest.pt> wrote: > Hi, > Have you already tried this out? > > select MAX(page_count_count) - MIN(page_count_count) from page_count group > by page_count_pdate. > > > Best, > Oliveiros Oliveiros, Thx that mostly works. I just tried it and on the days there is only 1 entry it is 0 since max is the same as min so max - min is 0. Is there a way to take in to account the 1 entry days? Again thx - I appreciate your help :) -wes
On 6/2/2010 12:31 PM, Wes James wrote:
> On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros
> <oliveiros.cristina@marktest.pt> wrote:
>
>> Hi,
>> Have you already tried this out?
>>
>> select MAX(page_count_count) - MIN(page_count_count) from page_count group
>> by page_count_pdate.
>>
>>
>> Best,
>> Oliveiros
>>
> Oliveiros,
>
> Thx that mostly works. I just tried it and on the days there is only
> 1 entry it is 0 since max is the same as min so max - min is 0. Is
> there a way to take in to account the 1 entry days?
>
> Again thx - I appreciate your help :)
>
> -wes
>
>
Put in a case
select
case when MAX(page_count_count) - MIN(page_count_count)> 0 then
MAX(page_count_count) - MIN(page_count_count)
else
MAX(page_count_count)
from page_count
group by page_count_pdate.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other
informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it
addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately.
Thank you.
Attachment
db2 has a group by rollup function.. does this exist in postgres? -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Oliveiros Sent: Wednesday, June 02, 2010 11:55 AM To: Wes James; pgsql-sql@postgresql.org Subject: Re: [SQL] how to construct sql Hi, Have you already tried this out? select MAX(page_count_count) - MIN(page_count_count) from page_count group by page_count_pdate. Best, Oliveiros ----- Original Message ----- From: "Wes James" <comptekki@gmail.com> To: <pgsql-sql@postgresql.org> Sent: Wednesday, June 02, 2010 5:48 PM Subject: [SQL] how to construct sql >I am grabbing a printer total and putting it in a table. The > page_count is continuously increasing: > > page_count_count page_count_pdate > 10 2010-05-10 > 20 2010-05-10 > 40 2010-05-11 > 60 2010-05-11 > 80 2010-05-11 > 100 2010-05-12 > 120 2010-05-12 > ..... > > and so on. > > I can do: > > select sum(page_count_count) from page_count group by page_count_pdate. > > and get a total for a day. But this is not the total I want. I want > the total page count for the day. This would mean getting the first > page count of the day and then subtracting that from last page_count > for the day. For 2010-05-11 above it would be > > 80 - 40 = 40 total for the day. Is there a way to do this with sql? > > thx, > > -wes > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entitynamed above. If the reader of the email is not the intended recipient or the employee or agent responsible for deliveringit to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmissionis strictly prohibited by the sender. If you have received this transmission in error, please delete the emailand immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
This is discussed in this Wiki: http://wiki.postgresql.org/wiki/Grouping_Sets -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Hiltibidal, Rob Sent: Wednesday, June 02, 2010 12:06 PM To: Oliveiros; Wes James; pgsql-sql@postgresql.org Subject: Re: [SQL] how to construct sql db2 has a group by rollup function.. does this exist in postgres? -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Oliveiros Sent: Wednesday, June 02, 2010 11:55 AM To: Wes James; pgsql-sql@postgresql.org Subject: Re: [SQL] how to construct sql Hi, Have you already tried this out? select MAX(page_count_count) - MIN(page_count_count) from page_count group by page_count_pdate. Best, Oliveiros ----- Original Message ----- From: "Wes James" <comptekki@gmail.com> To: <pgsql-sql@postgresql.org> Sent: Wednesday, June 02, 2010 5:48 PM Subject: [SQL] how to construct sql >I am grabbing a printer total and putting it in a table. The > page_count is continuously increasing: > > page_count_count page_count_pdate > 10 2010-05-10 > 20 2010-05-10 > 40 2010-05-11 > 60 2010-05-11 > 80 2010-05-11 > 100 2010-05-12 > 120 2010-05-12 > ..... > > and so on. > > I can do: > > select sum(page_count_count) from page_count group by page_count_pdate. > > and get a total for a day. But this is not the total I want. I want > the total page count for the day. This would mean getting the first > page count of the day and then subtracting that from last page_count > for the day. For 2010-05-11 above it would be > > 80 - 40 = 40 total for the day. Is there a way to do this with sql? > > thx, > > -wes > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entitynamed above. If the reader of the email is not the intended recipient or the employee or agent responsible for deliveringit to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmissionis strictly prohibited by the sender. If you have received this transmission in error, please delete the emailand immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Wed, Jun 2, 2010 at 2:44 PM, Justin Graf <justin@magwerks.com> wrote: > On 6/2/2010 12:31 PM, Wes James wrote: >> On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros >> <oliveiros.cristina@marktest.pt> wrote: >> >>> Hi, >>> Have you already tried this out? >>> >>> select MAX(page_count_count) - MIN(page_count_count) from page_count group >>> by page_count_pdate. >>> >>> >>> Best, >>> Oliveiros >>> >> Oliveiros, >> >> Thx that mostly works. I just tried it and on the days there is only >> 1 entry it is 0 since max is the same as min so max - min is 0. Is >> there a way to take in to account the 1 entry days? >> >> Again thx - I appreciate your help :) >> >> -wes >> >> > Put in a case > > select > case when MAX(page_count_count) - MIN(page_count_count)> 0 then > MAX(page_count_count) - MIN(page_count_count) > else > MAX(page_count_count) > from page_count > group by page_count_pdate. > Thx it is closer (with an end in the case): select case when MAX(page_count_count) - MIN(page_count_count) > 0 then MAX(page_count_count) - MIN(page_count_count) else MAX(page_count_count) end as day_max from page_count group by page_count_pdate order by page_count_pdate; the else puts out the total count on that day. I would need max(page_count_count) - max(page_count_count_of_previous_day) thx, -wes
On Wed, Jun 2, 2010 at 1:51 PM, Plugge, Joe R. <JRPlugge@west.com> wrote: > This is discussed in this Wiki: > > > http://wiki.postgresql.org/wiki/Grouping_Sets > This would sum the results and would be incorrect. I also get this error: select sum(page_count_count), page_count_pdate from page_count group by rollup(page_count_pdate); ERROR: function rollup(date) does not exist LINE 1: ...count), page_count_pdate from page_count group by rollup(pag... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. thx, -wes
On 6/2/2010 2:52 PM, Wes James wrote: > > **snip*** > Thx it is closer (with an end in the case): > > select > case when MAX(page_count_count) - MIN(page_count_count)> 0 then > MAX(page_count_count) - MIN(page_count_count) > else > MAX(page_count_count) > end as day_max > from page_count > group by page_count_pdate order by page_count_pdate; > > the else puts out the total count on that day. I would need > max(page_count_count) - max(page_count_count_of_previous_day) > > thx, > > -wes > A windowing query makes sense in this case which i'm not very good at All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
Attachment
On 2010-06-02, Wes James <comptekki@gmail.com> wrote: > On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros ><oliveiros.cristina@marktest.pt> wrote: >> Hi, >> Have you already tried this out? >> >> select MAX(page_count_count) - MIN(page_count_count) from page_count group >> by page_count_pdate. >> >> >> Best, >> Oliveiros > > Oliveiros, > > Thx that mostly works. I just tried it and on the days there is only > 1 entry it is 0 since max is the same as min so max - min is 0. Is > there a way to take in to account the 1 entry days? from your original requirement 0 is the correct answer.