Thread: 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: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
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.
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: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
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'
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: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
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 believeselect count(*) from prototype.customers, prototype.addresses wherecustomers.contactaddress = addresses.objectidand zipCode < '2716BN'; will work and give you an overall count. select zipcode, count(*) from prototype.customers, prototype.addresseswhere customers.contactaddress = addresses.objectidand zipCode <'2716BN' group by zipcode order by zipcode; should give you a list broken up with a count by zipcode in order of zipcode.