Re: [GENERAL] Query help
Try this: select distinct vendor_no, vendor_name from ap_vendors where vendor_no in ( select vendor_no from ap_vendors group by vendor_no having array_agg(company_code) @> ARRAY['BUR','EBC','SNJ']) On Wed, Oct 5, 2016 at 1:31 PM, Bret Stern wrote: > Good evening, > I'm curious about a way to ask the following question of my vendors > table. > > psuedo1 "select all vendors which exist in BUR and EBC and SNJ" > > and > psuedo2 "select all vendors which DO NOT exist in all three show rooms > > > The data is from a Sage accounting system which I pull out and place in a > pg db. What we are trying to do is identify which vendors are defined in > all three of our showrooms, and vice-versa, which ones are not. > > ap_vendors table > company_code character varying(10) NOT NULL, > ap_division_no character varying(2) NOT NULL, > vendor_no character varying(7) NOT NULL, > terms_code character varying(2), > vendor_name character varying(30), > address_line1 character varying(30), > address_line2 character varying(30), > address_line3 character varying(30), > city character varying(20), > state character varying(2), > zip_code character varying(10), > telephone_no character varying(17), > fax_no character varying(17), > CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, > vendor_no) > > sample records: > "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''"; > "92831";"''";"''" > "BUR";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''"; > "''";"90248";"''";"''" > "BUR";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";" > ''";"94545";"''";"''" > "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''"; > "92831";"''";"''" > "EBC";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''"; > "''";"90248";"''";"''" > "EBC";"00";"ARIZ01";"30";"Arizona Tile";"''";"''";"''";"''";"''" > ;"94550";"''";"''" > "SNJ";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";" > ''";"94545";"''";"''" > "SNJ";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''"; > "''";"90248";"''";"''" > "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''"; > "92831";"''";"''" > > What I need is a query which I can modify to return only vendors which > exists > in all three company_code columns ( BUR, EBC, SNJ) (they represent > showroom location) > > eg; exists in BUR, EBC, SNJ > ADEXU > AGORA > > OR > > modify the query to return only the vendors which DO NOT exist in all > three showrooms based on the first column company_code > > eg; > AKDOP only exists in BUR and SNJ > ARIZ01 only exists in EBC > > Thanks > Bret > > >
Re: [GENERAL] Query help
> On Oct 4, 2016, at 9:31 PM, Bret Stern > wrote: > > Good evening, > I'm curious about a way to ask the following question of my vendors > table. > > psuedo1 "select all vendors which exist in BUR and EBC and SNJ" > > and > psuedo2 "select all vendors which DO NOT exist in all three show rooms > > > The data is from a Sage accounting system which I pull out and place in a > pg db. What we are trying to do is identify which vendors are defined in > all three of our showrooms, and vice-versa, which ones are not. > > ap_vendors table > company_code character varying(10) NOT NULL, > ap_division_no character varying(2) NOT NULL, > vendor_no character varying(7) NOT NULL, > terms_code character varying(2), > vendor_name character varying(30), > address_line1 character varying(30), > address_line2 character varying(30), > address_line3 character varying(30), > city character varying(20), > state character varying(2), > zip_code character varying(10), > telephone_no character varying(17), > fax_no character varying(17), > CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, > vendor_no) > > sample records: > "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" > "BUR";"00";"AGORA";"30";"Agora Natural > Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" > "BUR";"00";"AKDOP";"30";"AKDO > Pacific";"''";"''";"''";"''";"''";"94545";"''";"''" > "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" > "EBC";"00";"AGORA";"30";"Agora Natural > Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" > "EBC";"00";"ARIZ01";"30";"Arizona > Tile";"''";"''";"''";"''";"''";"94550";"''";"''" > "SNJ";"00";"AKDOP";"30";"AKDO > Pacific";"''";"''";"''";"''";"''";"94545";"''";"''" > "SNJ";"00";"AGORA";"30";"Agora Natural > Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" > "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" > > What I need is a query which I can modify to return only vendors which exists > in all three company_code columns ( BUR, EBC, SNJ) (they represent showroom > location) > > eg; exists in BUR, EBC, SNJ > ADEXU > AGORA > > OR > > modify the query to return only the vendors which DO NOT exist in all > three showrooms based on the first column company_code > > eg; > AKDOP only exists in BUR and SNJ > ARIZ01only exists in EBC > > Thanks > Bret > > Not sure I like the schema but select vendor_no, count(*) from ap_vendors having count(*) = 3; and maybe count(*) < 3 is your second answer. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query help
Good evening, I'm curious about a way to ask the following question of my vendors table. psuedo1 "select all vendors which exist in BUR and EBC and SNJ" and psuedo2 "select all vendors which DO NOT exist in all three show rooms The data is from a Sage accounting system which I pull out and place in a pg db. What we are trying to do is identify which vendors are defined in all three of our showrooms, and vice-versa, which ones are not. ap_vendors table company_code character varying(10) NOT NULL, ap_division_no character varying(2) NOT NULL, vendor_no character varying(7) NOT NULL, terms_code character varying(2), vendor_name character varying(30), address_line1 character varying(30), address_line2 character varying(30), address_line3 character varying(30), city character varying(20), state character varying(2), zip_code character varying(10), telephone_no character varying(17), fax_no character varying(17), CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, vendor_no) sample records: "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" "BUR";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" "BUR";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"''";"94545";"''";"''" "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" "EBC";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" "EBC";"00";"ARIZ01";"30";"Arizona Tile";"''";"''";"''";"''";"''";"94550";"''";"''" "SNJ";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"''";"94545";"''";"''" "SNJ";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''" "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''" What I need is a query which I can modify to return only vendors which exists in all three company_code columns ( BUR, EBC, SNJ) (they represent showroom location) eg; exists in BUR, EBC, SNJ ADEXU AGORA OR modify the query to return only the vendors which DO NOT exist in all three showrooms based on the first column company_code eg; AKDOP only exists in BUR and SNJ ARIZ01 only exists in EBC Thanks Bret
Re: [GENERAL] Query help
If I read this correctly, you want the output sorted by config_id,start_day(day),start_time, thus: select config_id, start_day as day, start_time, end_time from config order by config_id, start_day, start_time; Cheers, Brent Wood >>> novice <[EMAIL PROTECTED]> 08/15/08 3:55 PM >>> Hi, I have a table select id, config_id, start_day, end_day, start_time, end_time from config; id | config_id | start_day | end_day | start_time | end_time -+---+---+-++-- 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00 I'd like to write a query to generate the following... is it possible at all? config_id | day | start_time | end_time ---+---+-+- 101 | Mon | 08:30:00 | 18:00:00 101 | Mon | 18:00:00 | 22:00:00 101 | Tue | 08:30:00 | 18:00:00 101 | Tue | 18:00:00 | 22:00:00 101 | Wed | 08:30:00 | 18:00:00 101 | Wed | 18:00:00 | 22:00:00 101 | Thu | 08:30:00 | 18:00:00 101 | Thu | 18:00:00 | 22:00:00 101 | Fri | 08:30:00 | 18:00:00 101 | Fri | 18:00:00 | 22:00:00 101 | Sat | 08:30:00 | 18:00:00 101 | Sat | 18:00:00 | 22:00:00 101 | Sun | 08:30:00 | 18:00:00 101 | Sun | 18:00:00 | 22:00:00 555 | Mon | 08:30:00 | 18:00:00 555 | Tue | 08:30:00 | 18:00:00 555 | Wed | 08:30:00 | 18:00:00 555 | Thu | 08:30:00 | 18:00:00 555 | Fri | 08:30:00 | 18:00:00 Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query help
2008/8/15 novice <[EMAIL PROTECTED]>: > Hi, > I have a table > > select id, config_id, start_day, end_day, start_time, end_time from config; > > id | config_id | start_day | end_day | start_time | end_time > -+---+---+-++-- > 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00 > 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00 > 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00 > > > > I'd like to write a query to generate the following... is it possible at all? > > config_id | day | start_time | end_time > ---+---+-+- > 101 | Mon | 08:30:00 | 18:00:00 > 101 | Mon | 18:00:00 | 22:00:00 > 101 | Tue | 08:30:00 | 18:00:00 > 101 | Tue | 18:00:00 | 22:00:00 > 101 | Wed | 08:30:00 | 18:00:00 > 101 | Wed | 18:00:00 | 22:00:00 > 101 | Thu | 08:30:00 | 18:00:00 > 101 | Thu | 18:00:00 | 22:00:00 > 101 | Fri | 08:30:00 | 18:00:00 > 101 | Fri | 18:00:00 | 22:00:00 > 101 | Sat | 08:30:00 | 18:00:00 > 101 | Sat | 18:00:00 | 22:00:00 > 101 | Sun | 08:30:00 | 18:00:00 > 101 | Sun | 18:00:00 | 22:00:00 > 555 | Mon | 08:30:00 | 18:00:00 > 555 | Tue | 08:30:00 | 18:00:00 > 555 | Wed | 08:30:00 | 18:00:00 > 555 | Thu | 08:30:00 | 18:00:00 > 555 | Fri | 08:30:00 | 18:00:00 > > Thanks > Solved: create TABLE weekday ( wd varchar(3), seq int ) INSERT INTO weekday (wd, seq) VALUES ('Mon', '1'), ('Tue', '2'), ('Wed', '3'), ('Thu', '4'), ('Fri', '5'), ('Sat', '6'), ('Sun', '7'); SELECT config.config_id, w.wd, config.start_time, config.end_time FROMconfig INNER JOIN weekday sON config.start_day= s.wd INNER JOIN weekday eON config.end_day = e.wd CROSS JOIN weekday w WHERE w.seq >= s.seq AND w.seq <= e.seq ORDER BY config.config_id, w.seq, w.wd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query help
Hi , below can work? select config_id, start_day as day, start_time, end_time from config union select config_id, end_day as day, start_time, end_time from config Best Regards, Hui Xie --- Axisoft Co. Ltd. Zhuhai Branch Tel: (86) 0756-3612121 8858 novice <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 15/08/2008 08:32 AM To pgsql-general@postgresql.org cc Subject [GENERAL] Query help Hi, I have a table select id, config_id, start_day, end_day, start_time, end_time from config; id | config_id | start_day | end_day | start_time | end_time -+---+---+-++-- 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00 I'd like to write a query to generate the following... is it possible at all? config_id | day | start_time | end_time ---+---+-+- 101 | Mon | 08:30:00 | 18:00:00 101 | Mon | 18:00:00 | 22:00:00 101 | Tue | 08:30:00 | 18:00:00 101 | Tue | 18:00:00 | 22:00:00 101 | Wed | 08:30:00 | 18:00:00 101 | Wed | 18:00:00 | 22:00:00 101 | Thu | 08:30:00 | 18:00:00 101 | Thu | 18:00:00 | 22:00:00 101 | Fri | 08:30:00 | 18:00:00 101 | Fri | 18:00:00 | 22:00:00 101 | Sat | 08:30:00 | 18:00:00 101 | Sat | 18:00:00 | 22:00:00 101 | Sun | 08:30:00 | 18:00:00 101 | Sun | 18:00:00 | 22:00:00 555 | Mon | 08:30:00 | 18:00:00 555 | Tue | 08:30:00 | 18:00:00 555 | Wed | 08:30:00 | 18:00:00 555 | Thu | 08:30:00 | 18:00:00 555 | Fri | 08:30:00 | 18:00:00 Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general *** The email has been scanned by AxiScan ***
[GENERAL] Query help
Hi, I have a table select id, config_id, start_day, end_day, start_time, end_time from config; id | config_id | start_day | end_day | start_time | end_time -+---+---+-++-- 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00 I'd like to write a query to generate the following... is it possible at all? config_id | day | start_time | end_time ---+---+-+- 101 | Mon | 08:30:00 | 18:00:00 101 | Mon | 18:00:00 | 22:00:00 101 | Tue | 08:30:00 | 18:00:00 101 | Tue | 18:00:00 | 22:00:00 101 | Wed | 08:30:00 | 18:00:00 101 | Wed | 18:00:00 | 22:00:00 101 | Thu | 08:30:00 | 18:00:00 101 | Thu | 18:00:00 | 22:00:00 101 | Fri | 08:30:00 | 18:00:00 101 | Fri | 18:00:00 | 22:00:00 101 | Sat | 08:30:00 | 18:00:00 101 | Sat | 18:00:00 | 22:00:00 101 | Sun | 08:30:00 | 18:00:00 101 | Sun | 18:00:00 | 22:00:00 555 | Mon | 08:30:00 | 18:00:00 555 | Tue | 08:30:00 | 18:00:00 555 | Wed | 08:30:00 | 18:00:00 555 | Thu | 08:30:00 | 18:00:00 555 | Fri | 08:30:00 | 18:00:00 Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query help
can u refer to row?? howto select * from table where row(#2) like 'J%'?? i wanted to test column storing but not ok as no row refer name/id. many thank yous sincerely siva Original Message Subject: Re: [GENERAL] query help From: [EMAIL PROTECTED] Date: Thu, September 13, 2007 11:46 am To: Alexander Staubo <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org many apologees. right link http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php i flip row to column if ok. but howto query?? sincerely siva Original Message ---- Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:38 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query help
[EMAIL PROTECTED] wrote: hello i add more column not row for new user. i want all "last like 'J%'". I get the feeling that the result as you've laid it out is not what we all think it is. For example: >>table is >>+---+---+--+---+ >>| id | one | two | three | >>+---+---+--+---+ >>| first | Jack | Jill | Mary | >>| last | Ja | Ji | Ma | >>+---+---+--+---+ I took that to meant that you have columns 'id', 'one', two', three', and that 'first' & 'last' are field values. However, it now seems that 'first' & 'last' are column names. If so, this makes no sense. I think what you wanted to give us was: +---+---+--+ | id | first | last | +---+---+--+ | one | Jack | Ja | | two | Jill | Ji | | three | Mary | Ma | result: +---+---+--+ | id | first | last | +---+---+--+ | one | Jack | Ja | | two | Jill | Ji | So, the query you want is, in fact: SELECT * FROM your_table WHERE last LIKE ('J%'); If that's not working for you, it's perhaps because you have rows for columns and columns for rows. http://www.nabble.com/an-other-provokative-question---tf4394285.html sincerely siva What the heck does this have to do with anything? Please don't top-post. brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] query help
many apologees. right link http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php i flip row to column if ok. but howto query?? sincerely siva Original Message Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:38 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query help
[EMAIL PROTECTED] wrote: > hello > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html > sincerely > siva You add a new _column_ for each user?!? That is hideously broken in so many ways. It makes the trivially easy query you are trying to write rather complicated, prevents you from being able to use indexes for either constraints or performance and requires you to change your table definition anytime you add data. And that's just for starters. Fix your table so it has three columns (id, first, last). Then your query is as easy as: select id,first,last from foo where last like 'J%'; Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query help
hello i add more column not row for new user. i want all "last like 'J%'". http://www.nabble.com/an-other-provokative-question---tf4394285.html sincerely siva Original Message ---- Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:17 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > hello > > table is > +---+---+--+---+ > | id | one | two | three | > +---+---+--+---+ > | first | Jack | Jill | Mary | > | last | Ja | Ji | Ma | > +---+---+--+---+ > > result is > ++---+---+ > | id | one | two | > ++---+---+ > | first | Jack | Jill | > | last | Ja | Ji | > ++---+---+ > > query is?? Unless I am missing something crucial, this is SQL 101: select id, one, two from foo; Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > query is?? http://www.w3schools.com/sql/default.asp ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > hello > > table is > +---+---+--+---+ > | id | one | two | three | > +---+---+--+---+ > | first | Jack | Jill | Mary | > | last | Ja | Ji | Ma | > +---+---+--+---+ > > result is > ++---+---+ > | id | one | two | > ++---+---+ > | first | Jack | Jill | > | last | Ja | Ji | > ++---+---+ > > query is?? Unless I am missing something crucial, this is SQL 101: select id, one, two from foo; Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] query help
hello table is +---+---+--+---+ | id | one | two | three | +---+---+--+---+ | first | Jack | Jill | Mary | | last | Ja | Ji | Ma | +---+---+--+---+ result is ++---+---+ | id | one | two | ++---+---+ | first | Jack | Jill | | last | Ja | Ji | ++---+---+ query is?? sincerely siva ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] query help
On 8/14/07, Kirk Wythers <[EMAIL PROTECTED]> wrote: > > I need some help with rewriting a query. I have a fairly complicated query > (for me anyway) that dumps daily climate data, filling in missing data with > monthly averages (one line per day). > > I want to output monthly averages (one line per month). I am having a hard > time wrapping my head around this. Particularly how to deal with the doy > column (day of year). I have tried several approaches and my forehead is > starting to get my keyboard bloody. I think this came up on IRC today, so perhaps this is only for the archives' sake, but you want to do something like this: Assuming you have a table as follows: CREATE TABLE climate_data ( measurement_time timestamp, measurement_value integer); ...and you insert data into it regularly, you can get the average measurement over a period of time with date_trunc(), which will truncate a date or timestamp value to match whatever precision you specify. For example, see the following: eggyknap=# select date_trunc('month', now()); date_trunc 2007-08-01 00:00:00-06 (1 row) Note: the -06 at the end means I'm in mountain time. So if you want to get the average measurement over a month's time, you need to do something like this: SELECT DATE_TRUNC('MONTH', measurement_time), AVG(measurement_value) FROM climate_data GROUP BY DATE_TRUNC('MONTH', measurement_time); This will chop all the measurement_time values down to the month the measurement was taken in, put all measurements in groups based on the resulting value, and take the average measurement_value from each group. - Josh ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] query help
I need some help with rewriting a query. I have a fairly complicated query (for me anyway) that dumps daily climate data, filling in missing data with monthly averages (one line per day). I want to output monthly averages (one line per month). I am having a hard time wrapping my head around this. Particularly how to deal with the doy column (day of year). I have tried several approaches and my forehead is starting to get my keyboard bloody. Thanks in advance for any suggestions. Here is the daily query: SELECT CASE WHEN w.station_id = site_near.station_id THEN w.obs_id ELSE s.obs_id END AS obs_id, site_near.station_id, site_near.longname, w.year, w.doy, --replace missing values (-999) with the monthly average CASE w.tmax WHEN -999 THEN avgtmax.avg ELSE w.tmax END, CASE w.tmin WHEN -999 THEN avgtmin.avg ELSE w.tmin END, CASE s.par WHEN -999 THEN avgpar.avg ELSE s.par END, CASE w.precip WHEN -999 THEN avgprecip.avg ELSE w.precip END FROM site_near INNER JOIN solar s ON (site_near.ref_solar_station_id = s.station_id AND site_near.obs_year = s.year) INNER JOIN weather w ON (site_near.ref_weather_station_id = w.station_id AND site_near.obs_year = w.year AND s.date = w.date) INNER JOIN (SELECT MONTH, round(avg(tmax)::numeric, 2) AS avg FROM weather WHEREtmax != -999 GROUP BY MONTH) AS avgtmax ON (w.month = avgtmax.month) INNER JOIN (SELECT MONTH, round(avg(tmin)::numeric, 2) AS avg FROM weather WHEREtmin != -999 GROUP BY MONTH) AS avgtmin ON (w.month = avgtmin.month) INNER JOIN (SELECT MONTH, round(avg(par)::numeric, 2) AS avg FROM solar WHEREpar != -999 GROUP BY MONTH) AS avgpar ON (s.month = avgpar.month) INNER JOIN (SELECT MONTH, round(avg(precip)::numeric, 2) AS avg FROM weather WHEREprecip != -999 GROUP BY MONTH) AS avgprecip ON (w.month = avgprecip.month) --select station to output climate data by id number WHERE w.station_id = 219101
Re: [GENERAL] Query help
Richard Huxton wrote: Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). You'll want something like: SELECT sch_id, sch_for_table, sch_ref_id, SUM(sch_instances) AS tot_instances ... GROUP BY sch_id, sch_for_table, sch_ref_id ORDER BY tot_instances DESC; The key word to search the manuals on is "aggregates" (sum(), count() etc). This is *exactly* the pointer I needed, thank you! Sad thing is that I even used "GROUP BY" before... had just forgotten about it. ^_^; Madison ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Query help
Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). You'll want something like: SELECT sch_id, sch_for_table, sch_ref_id, SUM(sch_instances) AS tot_instances ... GROUP BY sch_id, sch_for_table, sch_ref_id ORDER BY tot_instances DESC; The key word to search the manuals on is "aggregates" (sum(), count() etc). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query help
Try SELECT DISTINCT rather than SELECT That should return a result with unique records. Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). Is this possible or would I need to add this logic in my program? I'd rather do it in PostgreSQL though, if I could. Here is the 'search_index' table I am using: db=> \d search_index Table "public.search_index" Column | Type | Modifiers ---+-+--- sch_id| integer | not null default nextval('sch_seq'::regclass) sch_keyword | text| not null sch_instances | integer | not null default 1 sch_for_table | text| not null sch_ref_id| integer | not null Indexes: "search_index_pkey" PRIMARY KEY, btree (sch_id) Thanks in advance to any help you might be able to give me! Madison ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Query help
Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). Is this possible or would I need to add this logic in my program? I'd rather do it in PostgreSQL though, if I could. Here is the 'search_index' table I am using: db=> \d search_index Table "public.search_index" Column | Type | Modifiers ---+-+--- sch_id| integer | not null default nextval('sch_seq'::regclass) sch_keyword | text| not null sch_instances | integer | not null default 1 sch_for_table | text| not null sch_ref_id| integer | not null Indexes: "search_index_pkey" PRIMARY KEY, btree (sch_id) Thanks in advance to any help you might be able to give me! Madison ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query Help
Tom Lane wrote: It looks like you have a stale plan for that ON DELETE SET NULL constraint. Was there perhaps an index on operator_id that you removed? Postgres is not very good about flushing cached plans when you change table schemas (something I hope will be fixed in 8.3). If that is the problem, starting a fresh session would be enough to take care of it. regards, tom lane Thanks Tom. You were, as usual, correct! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query Help
Howard Cole <[EMAIL PROTECTED]> writes: > If I try the following query: > delete from operator where operator_id=283; > I get the following error message: > ERROR: could not open relation with OID 438427 > SQL state: XX000 > Context: SQL statement "UPDATE ONLY "public"."timesheet_booking" SET > "operator_id" = NULL WHERE "operator_id" = $1" It looks like you have a stale plan for that ON DELETE SET NULL constraint. Was there perhaps an index on operator_id that you removed? Postgres is not very good about flushing cached plans when you change table schemas (something I hope will be fixed in 8.3). If that is the problem, starting a fresh session would be enough to take care of it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Query Help
Hi All, I am getting an error I do not understand from the following setup CREATE TABLE timesheet_booking ( timesheet_booking_id bigserial NOT NULL, operator_id integer, booking_item_id integer, "day" date NOT NULL, minutes integer NOT NULL, CONSTRAINT timesheet_booking_pkey PRIMARY KEY (timesheet_booking_id), CONSTRAINT timesheet_booking_booking_item_id_fkey FOREIGN KEY (booking_item_id) REFERENCES booking_item (booking_item_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, CONSTRAINT timesheet_booking_operator_id_fkey FOREIGN KEY (operator_id) REFERENCES "operator" (operator_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL ) WITHOUT OIDS; CREATE UNIQUE INDEX timesheet_booking_unique_idx ON timesheet_booking USING btree (operator_id, booking_item_id, "day"); Now the timesheet booking contains the following data: timesheet_booking_id, operator_id,booking_item_id,day,minutes 2;284;1;"2007-01-18";10 4;284;2;"2007-01-18";10 If I try the following query: delete from operator where operator_id=283; I get the following error message: ERROR: could not open relation with OID 438427 SQL state: XX000 Context: SQL statement "UPDATE ONLY "public"."timesheet_booking" SET "operator_id" = NULL WHERE "operator_id" = $1" If I try this sql statement directly it works OK. What does this error mean and how can I get rid of it? Thanks Howard. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query help
Jeff Patterson wrote: > This seems like such a basic function that I'm sure I am missing something > fundamental. I have a table, say xref, whose columns are primary key > values for other tables. Some of theses keys may be NULL for a given row > in xref. I want to create a query that returns the corresponding entries > in the other tables. > > xref: > drug_id | function_id|syst_id |text_id > -- > d0001 | 2 | 3| 3423 > d0001 | 5 || 5678 > d0056 | 3 | 5| > > system_id: > syst_id | syst_desc > --- > 3 | renal > 4 | hepatic > 5 | respiratory > > clinical_text: > text_id| clinical_text > --- > 3423 | 'some medical mumbo jumbo' > 5678 | 'more of the same' > > I want the syst_desc and clinical_text (plus other similar data from > tables not shown) given a drug_id and function_id. > > Any help? If I understand you correctly, you want the query to return the syst_desc and clinical_text fields where either or both are available? (and not just where both are available)? If so a LEFT JOIN may be your best friend. The statement could look like this: SELECT system_id.syst_desc, clinical_text.clinical_text FROM xref LEFT JOIN system_id ON xref.syst_id=system_id.syst_id LEFT JOIN clinical_text ON xref.text_id=clinical_text.text_id WHERE xref.drug_id=? <- insert query value here AND xref.function_id=? <- and here (disclaimer: statement untested) The values not available will be returned as NULL. > Thanks a bunch, a bunch of what? ;-) HTH Ian Barwick -- Ian Barwick - Developer http://www.akademie.de Remove SUNGLASSES to reply ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] query help
On Fri, 7 Sep 2001, Jeff Patterson wrote: > This seems like such a basic function that I'm sure I am missing something > fundamental. I have a table, say xref, whose columns are primary key values for > other tables. Some of theses keys may be NULL for a given row in xref. I want > to create a query that returns the corresponding entries in the other tables. > > xref: > drug_id | function_id|syst_id |text_id > -- > d0001 | 2 | 3| 3423 > d0001 | 5 || 5678 > d0056 | 3 | 5| > > system_id: > syst_id | syst_desc > --- > 3 | renal > 4 | hepatic > 5 | respiratory > > clinical_text: > text_id| clinical_text > --- > 3423 | 'some medical mumbo jumbo' > 5678 | 'more of the same' > > I want the syst_desc and clinical_text (plus other similar data from tables not > shown) given a drug_id and function_id. Something like (untested) select drug_id, function_id, syst_desc, clinical_text from (xref left outer join system_id using (syst_id)) left outer join clinical_text using (text_id); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] query help
This seems like such a basic function that I'm sure I am missing something fundamental. I have a table, say xref, whose columns are primary key values for other tables. Some of theses keys may be NULL for a given row in xref. I want to create a query that returns the corresponding entries in the other tables. xref: drug_id | function_id|syst_id |text_id -- d0001 | 2 | 3| 3423 d0001 | 5 || 5678 d0056 | 3 | 5| system_id: syst_id | syst_desc --- 3 | renal 4 | hepatic 5 | respiratory clinical_text: text_id| clinical_text --- 3423 | 'some medical mumbo jumbo' 5678 | 'more of the same' I want the syst_desc and clinical_text (plus other similar data from tables not shown) given a drug_id and function_id. Any help? Thanks a bunch, Jeff Patterson The Melanoma Patients' Information Page http://www.mpip.org -- Jeff Patterson Site Administrator The Melanoma Patients' Information Page http://www.mpip.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] query help
For one specific label: select TableB.label from TableA, TableB where TableA.idA=TableB.idA and TableA.name='test2'; To get a list of name-label pairs: select TableA.name, TableB.label from TableA, TableB where TableA.idA=TableB.idA order by TableA.name, TableB.label; "G.L. Grobe" <[EMAIL PROTECTED]> on 08/30/2001 07:45:20 PM To: [EMAIL PROTECTED] cc:(bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] query help Hi all, How would I get TableB.label if all I know about is TableA.name? I was thinking about a query on TableA first, then within the while (rs.next()), I'd do another query using the result set from A on B, but I don't like that much, and it seems kind of slow. Any help much appreciated! TableA idA | name | class - 11test1 1 12test2 1 13test3 2 14test4 2 TableB idB | idA | label 1 11 nameA 2 12 nameB 3 13 nameC 4 14 nameD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly