Re: [SQL] How to get a count() where column < ''?
On Sun, 4 Dec 2005, Joost Kraaijeveld wrote: > Hi Stephan, > > On Sun, 2005-12-04 at 13:33 -0800, Stephan Szabo wrote: > > > SELECT COUNT(customers.objectid) FROM prototype.customers, > > > prototype.addresses > > > WHERE > > > customers.contactaddress = addresses.objectid > > > AND > > > zipCode < '2716BN' > > > ORDER By zipCode, houseNumber > > > In a non-grouped query like the above, I don't think that the order by is > > meaningful. You only get one row back anyway without a group by, and > > there's no single zipCode or houseNumber to associate with the row. > > > What do you mean by a non-grouped query? The query below gives the same > error: A query without a group by, in other words one on which the count is done over the entire set of rows that pass the where clause. > SELECT zipcode, COUNT(*) FROM prototype.customers, prototype.addresses > WHERE > customers.contactaddress = addresses.objectid > AND > zipCode < '2716BN' Yes, because without a group by there's one count and it has no associated zipcode to put in the select list. I believe select count(*) from prototype.customers, prototype.addresses where customers.contactaddress = addresses.objectid and zipCode < '2716BN'; will work and give you an overall count. select zipcode, count(*) from prototype.customers, prototype.addresses where customers.contactaddress = addresses.objectid and zipCode < '2716BN' group by zipcode order by zipcode; should give you a list broken up with a count by zipcode in order of zipcode. ---(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: [SQL] How to get a count() where column < ''?
Hi Stephan, > A query without a group by, in other words one on which the count is done > over the entire set of rows that pass the where clause. OK. > I believe > select count(*) from prototype.customers, prototype.addresses where > customers.contactaddress = addresses.objectid and zipCode < '2716BN'; > will work and give you an overall count. The working query (I did not count if the answer was correct ;-)) appears to be: SELECT COUNT(zipcode) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '1234ab' -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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: [SQL] How to get a count() where column < ''?
Joost Kraaijeveld wrote: Hi Stephan, On Sun, 2005-12-04 at 13:33 -0800, Stephan Szabo wrote: SELECT COUNT(customers.objectid) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '2716BN' ORDER By zipCode, houseNumber In a non-grouped query like the above, I don't think that the order by is meaningful. You only get one row back anyway without a group by, and there's no single zipCode or houseNumber to associate with the row. What do you mean by a non-grouped query? The query below gives the same error: SELECT zipcode, COUNT(*) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '2716BN' Try SELECT COUNT(*) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '2716BN' ---(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: [SQL] How to get a count() where column < ''?
Hi Stephan, On Sun, 2005-12-04 at 13:33 -0800, Stephan Szabo wrote: > > SELECT COUNT(customers.objectid) FROM prototype.customers, > > prototype.addresses > > WHERE > > customers.contactaddress = addresses.objectid > > AND > > zipCode < '2716BN' > > ORDER By zipCode, houseNumber > In a non-grouped query like the above, I don't think that the order by is > meaningful. You only get one row back anyway without a group by, and > there's no single zipCode or houseNumber to associate with the row. > What do you mean by a non-grouped query? The query below gives the same error: SELECT zipcode, COUNT(*) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '2716BN' -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to get a count() where column < ''?
On Sun, 4 Dec 2005, Joost Kraaijeveld wrote: > Hi, > > I want the number of customers that have a zipCode smaller tha a given > value. The foolowing query doe snot work : I get an error (ERROR: > column "addresses.zipcode" must appear in the GROUP BY clause or be used > in an aggregate function) and I do not know how to solve it. > > SELECT COUNT(customers.objectid) FROM prototype.customers, > prototype.addresses > WHERE > customers.contactaddress = addresses.objectid > AND > zipCode < '2716BN' > ORDER By zipCode, houseNumber > > Anyone an idea? In a non-grouped query like the above, I don't think that the order by is meaningful. You only get one row back anyway without a group by, and there's no single zipCode or houseNumber to associate with the row. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] How to get a count() where column < ''?
Hi, I want the number of customers that have a zipCode smaller tha a given value. The foolowing query doe snot work : I get an error (ERROR: column "addresses.zipcode" must appear in the GROUP BY clause or be used in an aggregate function) and I do not know how to solve it. SELECT COUNT(customers.objectid) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode < '2716BN' ORDER By zipCode, houseNumber Anyone an idea? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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: [SQL] question using 'between' in a sql query
thanks... as is so often the case I discovered
date_trunc('day', insertdate) between '11/20/2005' AND
'11/20/2005' also works
It wasn't until I pressed the send button that I
thought of the solution. I have thought of creating a
faux 'send' button for my email software... yours is a
little easier to read (human)..
Thanks again,
Ted
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore
> Petrosky wrote:
> > if 'between' is inclusive of the start and end
> dates why doesn't
> > this query result in all the rows on '11/20/2005'?
>
> EXPLAIN shows what happens when you compare a date
> against a timestamp:
>
> Filter: ((insertdate >= '2005-11-20
> 00:00:00-08'::timestamp with time zone)
> AND (insertdate <= '2005-11-20
> 00:00:00-08'::timestamp with time zone))
>
> One way around this is to cast the timestamp column
> to date:
>
> SELECT employeecode
> FROM mytable
> WHERE insertdate::date BETWEEN '11/20/2005' AND
> '11/20/2005';
>
> --
> Michael Fuhr
>
__
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] SQL Statement Help needed
Michael Avila <[EMAIL PROTECTED]> schrieb: > I am not much of a SQL guru so I am having trouble trying to figure out how > to format a SQL statement. > > I have a table with members named members. Each member has only 1 record. > Then I have a table with member telephone numbers in it name > membertelephones. A member can have more than one telephone number (home, > work, cell, pager, fax, etc.). I want to print out the telephone numbers of test=# select * from member; id | name +- 1 | andreas 2 | anja (2 rows) test=# select * from member_number ; id | number + 1 | 12345 1 | 45678 2 | 232323 (3 rows) > the members. Is it possible to do it in one SQL statement like with a JOIN > or something or do I need to get the members and then loop through the > membertelephones to get the telephone numbers? Is it possible to do a JOIN > with a table with one record with a table with multiple records? > > SELECT * FROM member > > SELECT * FROM membertelephone WHERE member_id = the id from the above SELECT test=# select * from member_number where id = (select id from member where name = 'andreas'); id | number + 1 | 12345 1 | 45678 (2 rows) Btw.: you should use referential integrity between this tables: t=# \d member_number Table "public.member_number" Column | Type| Modifiers +---+--- id | integer | number | character varying | Foreign-key constraints: "member_number_id_fkey" FOREIGN KEY (id) REFERENCES member(id) Btw.: on your mail there was a odd attachment 'winmail.dat' HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SQL Statement Help needed
I am not much of a SQL guru so I am having trouble trying to figure out how to format a SQL statement. I have a table with members named members. Each member has only 1 record. Then I have a table with member telephone numbers in it name membertelephones. A member can have more than one telephone number (home, work, cell, pager, fax, etc.). I want to print out the telephone numbers of the members. Is it possible to do it in one SQL statement like with a JOIN or something or do I need to get the members and then loop through the membertelephones to get the telephone numbers? Is it possible to do a JOIN with a table with one record with a table with multiple records? SELECT * FROM member SELECT * FROM membertelephone WHERE member_id = the id from the above SELECT Thanks for the help. Mike <> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
