[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_countpage_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
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" To: 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_countpage_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
Re: [SQL] how to construct sql
On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
On 6/2/2010 12:31 PM, Wes James wrote: > On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros > 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. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
db2 has a group by rollup function.. does this exist in postgres? -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@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" To: 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_countpage_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:postmas...@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
[SQL] ODBC-link returns zero rows, allthough query should return 3 rows
Hi,
We are trying to make use of module ODBC-link. We follow the
instructions as read in README.TXT, including the given examples.
Connecting to an external Oracle database and running a query using unixODBC is
successful:
===
$ isql -v ONT_KIS sbm_beheer bioinf
+---+
| Connected!|
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---+
SQL> select * from mytable;
+--+---+--+
| ID | T | D
|
+--+---+--+
| 1| FIRST TEXT| 1.3
|
| 2| SECOND TEXT | 4
|
| 3| THIRD TEXT| 4
|
+--+---+--+
SQLRowCount returns -1
3 rows fetched
===
Now trying the same, using ODBClink result in zero rows:
===
$ psql oratest psql (8.4.3)
Type "help" for help.
oratest=# select odbclink.connect('ONT_KIS', 'sbm_beheer', 'bioinf');
connect
-
1
(1 row)
oratest=# select * FROM odbclink.query(1, 'SELECT id, t, d FROM mytable WHERE
id=2') as result(id float, t text, d float);
id | t | d
+---+---
(0 rows)
===
Unfortunately the developers of the ODBClink do not answer my mails, so I ask
you if someone out there has any clue. It will also be appreciated if anyone
can point me to a more appropriate mailing list.
We're running PostgreSQL 8.4.3, ODBC-link 1.0.
TIA,
Reinier Suurenbroek
Keygene N.V.
P.O. Box 216
6700 AE Wageningen
The Netherlands
Tel. (+31) 317 46 68 66
Fax. (+31) 317 42 49 39
Web: http://www.keygene.com/
The information contained in this message, and attachments if any, may be
privileged and/or confidential and is intended to be received only by persons
entitled to receive such information. Use of any part of this message and/or
its attachments if any, in any other way than as explicitly stated by the
sender is strictly prohibited. Should you receive this message unintentionally
please notify the sender immediately, and delete it together with all
attachments, if any. Thank you.
The transmission of messages and/or information via the Internet is not secured
and may be intercepted by third parties. KeyGene assumes no liability for any
damage caused by any unintentional disclosure and/or use of the content of this
message and attachments if any.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Do not understand "SETOF RECORD" - therefore can not use ODBC-link
Hi,
2010/5/27 Tom Lane :
> Harrie Rodenbach writes:
>> = oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as
>> result(id int4, t text, d decimal); = ERROR: syntax error at or near
>> "(" = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as
>> result(id int4, ...
>
> You need that to be select * from odbclink.query ...
> the "as" business is only allowed in FROM clause.
>
> regards, tom lane
>
That did the trick, thank you Tom. Next problem will be described in a
new thread.
--
Met vriendelijke groet / with kind regards,
Harrie Rodenbach
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] return %ROWTYPE from function
Hello, All! I've tried to do following: create table test ( id BIGSERIAL NOT NULL, constraint pk_test primary key (id), val BIGINT NOT NULL ); create or replace function get_rec (in p_id test.id%TYPE) returns test %ROWTYPE as $$ declare retval test%ROWTYPE; begin select * from test into retval where id = $1; return retval; end; $$ language 'plpgsql'; I got an error message: "sysntax error at or near ROWTYPE" Is it possible to return ONE record that has the same type as table? I Oracle it is work fine Any Ideas or work around? Thanks. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] return %ROWTYPE from function
hi Anton, works fine if you write: create or replace function get_rec (in p_id test.id%TYPE) returns test as $$ -- regards, jr. (j...@tailorware.org.uk) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
hi Louis-David,
tmp := nextval('cabin_type_id_cabin_type_seq');
seems to me you're adding a newly created key value (for which there
isn't a record yet).
--
regards, jr. (j...@tailorware.org.uk)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
This is discussed in this Wiki: http://wiki.postgresql.org/wiki/Grouping_Sets -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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" To: 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_countpage_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:postmas...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
On Wed, Jun 2, 2010 at 2:44 PM, Justin Graf wrote: > On 6/2/2010 12:31 PM, Wes James wrote: >> On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros >> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
On Wed, Jun 2, 2010 at 1:51 PM, Plugge, Joe R. 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct 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. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
