pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get a count() where column < ''?
6+ messages / 3 participants
[nested] [flat]
* How to get a count() where column < ''?
@ 2005-12-04 21:12 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Joost Kraaijeveld @ 2005-12-04 21:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a count() where column < ''?
@ 2005-12-04 21:33 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2005-12-04 21:33 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a count() where column < ''?
@ 2005-12-04 21:43 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 6+ messages in thread
From: Joost Kraaijeveld @ 2005-12-04 21:43 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a count() where column < ''?
@ 2005-12-04 22:17 Jim Johannsen <jjsa@gvtc.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 6+ messages in thread
From: Jim Johannsen @ 2005-12-04 22:17 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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'
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a count() where column < ''?
@ 2005-12-04 22:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2005-12-04 22:29 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get a count() where column < ''?
@ 2005-12-04 22:36 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joost Kraaijeveld @ 2005-12-04 22:36 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-12-04 22:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-04 21:12 How to get a count() where column < ''? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-04 21:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-12-04 21:43 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-04 22:17 ` Jim Johannsen <jjsa@gvtc.com>
2005-12-04 22:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-12-04 22:36 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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