[SQL] How to get a count() where column ''?

2005-12-04 Thread Joost Kraaijeveld 
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] How to get a count() where column ''?

2005-12-04 Thread Stephan Szabo 
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

Re: [SQL] How to get a count() where column ''?

2005-12-04 Thread Joost Kraaijeveld 
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 ''?

2005-12-04 Thread Jim Johannsen 

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 ''?

2005-12-04 Thread Joost Kraaijeveld 
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 ''?

2005-12-04 Thread Stephan Szabo 

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