pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to construct sql
10+ messages / 6 participants
[nested] [flat]
* how to construct sql
@ 2010-06-02 16:48 Wes James <comptekki@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Wes James @ 2010-06-02 16:48 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 16:55 Oliveiros <oliveiros.cristina@marktest.pt>
parent: Wes James <comptekki@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Oliveiros @ 2010-06-02 16:55 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; pgsql-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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 17:06 Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
parent: Oliveiros <oliveiros.cristina@marktest.pt>
1 sibling, 1 reply; 10+ messages in thread
From: Hiltibidal, Rob @ 2010-06-02 17:06 UTC (permalink / raw)
To: Oliveiros <oliveiros.cristina@marktest.pt>; Wes James <comptekki@gmail.com>; pgsql-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 entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 17:31 Wes James <comptekki@gmail.com>
parent: Oliveiros <oliveiros.cristina@marktest.pt>
1 sibling, 1 reply; 10+ messages in thread
From: Wes James @ 2010-06-02 17:31 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 19:51 Plugge, Joe R. <JRPlugge@west.com>
parent: Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
0 siblings, 1 reply; 10+ messages in thread
From: Plugge, Joe R. @ 2010-06-02 19:51 UTC (permalink / raw)
To: Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>; Oliveiros <oliveiros.cristina@marktest.pt>; Wes James <comptekki@gmail.com>; pgsql-sql
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 entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 19:52 Wes James <comptekki@gmail.com>
parent: Justin Graf <justin@magwerks.com>
1 sibling, 0 replies; 10+ messages in thread
From: Wes James @ 2010-06-02 19:52 UTC (permalink / raw)
To: Justin Graf <justin@magwerks.com>; +Cc: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 20:04 Wes James <comptekki@gmail.com>
parent: Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 0 replies; 10+ messages in thread
From: Wes James @ 2010-06-02 20:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: how to construct sql
@ 2010-06-02 20:44 Justin Graf <justin@magwerks.com>
0 siblings, 2 replies; 10+ messages in thread
From: Justin Graf @ 2010-06-02 20:44 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; +Cc: pgsql-sql
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 our proprietary 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 information proprietary 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 that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
Attachments:
justin.vcf (text/x-vcard, 256B) download
begin:vcard
fn:Justin Graf
n:Graf;Justin
org:Magwerks Corp
adr:;;501 Commerce Drive;Danville ;IN;46122;USA
email;internet:justin@magwerks.com
tel;work:317-241-8011 ext 703
tel;fax:317-241-8015
x-mozilla-html:FALSE
url:www.magwerks.com
version:2.1
end:vcard
* Re: how to construct sql
@ 2010-06-02 21:45 Justin Graf <justin@magwerks.com>
parent: Justin Graf <justin@magwerks.com>
1 sibling, 0 replies; 10+ messages in thread
From: Justin Graf @ 2010-06-02 21:45 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; +Cc: pgsql-sql
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 our proprietary 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 information proprietary 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 that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
Attachments:
justin.vcf (text/x-vcard, 256B) download
begin:vcard
fn:Justin Graf
n:Graf;Justin
org:Magwerks Corp
adr:;;501 Commerce Drive;Danville ;IN;46122;USA
email;internet:justin@magwerks.com
tel;work:317-241-8011 ext 703
tel;fax:317-241-8015
x-mozilla-html:FALSE
url:www.magwerks.com
version:2.1
end:vcard
* Re: how to construct sql
@ 2010-06-04 10:01 Jasen Betts <jasen@xnet.co.nz>
parent: Wes James <comptekki@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jasen Betts @ 2010-06-04 10:01 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-06-04 10:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-02 16:48 how to construct sql Wes James <comptekki@gmail.com>
2010-06-02 16:55 ` Oliveiros <oliveiros.cristina@marktest.pt>
2010-06-02 17:06 ` Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
2010-06-02 19:51 ` Plugge, Joe R. <JRPlugge@west.com>
2010-06-02 20:04 ` Wes James <comptekki@gmail.com>
2010-06-02 17:31 ` Wes James <comptekki@gmail.com>
2010-06-04 10:01 ` Jasen Betts <jasen@xnet.co.nz>
2010-06-02 20:44 Re: how to construct sql Justin Graf <justin@magwerks.com>
2010-06-02 19:52 ` Wes James <comptekki@gmail.com>
2010-06-02 21:45 ` Justin Graf <justin@magwerks.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox