multiple count functions in a select statement

On Thu, 2004-05-27 at 17:38, Jeannie Stevenson wrote:
...

In MSSQL I would use

select
count_1=(select count(ad_code) AS "CP" from leads where ad_code =
'555'),
count_2=(select count(ad_code) AS HED" from leads where ad_code =
'222'),
etc

SELECT
(select count(ad_code) AS "CP"
from leads where ad_code = '555') AS count_1,
(select count(ad_code) AS HED"
from leads where ad_code = '222') AS count_2,
...etc...

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"How precious also are thy thoughts unto me, O God! how
great is the sum of them! If I should count them, they
are more in number than the sand; when I awake, I am
still with thee." Psalms 139: 17,18

On Monday 31 May 2004 04:32 pm, Oliver Elphick wrote:

On Thu, 2004-05-27 at 17:38, Jeannie Stevenson wrote:
...

In MSSQL I would use

select
count_1=(select count(ad_code) AS "CP" from leads where ad_code =
'555'),
count_2=(select count(ad_code) AS HED" from leads where ad_code =
'222'),
etc

SELECT
(select count(ad_code) AS "CP"
from leads where ad_code = '555') AS count_1,
(select count(ad_code) AS HED"
from leads where ad_code = '222') AS count_2,
...etc...

Or, if you want a count of every ad_code:

SELECT ad_code, COUNT(ad_code) FROM leads GROUP BY ad_code;

-miker

If I understand what you're asking try:

select count_1, count_2 from
(select count(*) AS count_1 from leads where ad_code = '555'),
(select count(*) AS count_2 from leads where ad_code = '222')
;

Duane

-----Original Message-----
From: Jeannie Stevenson [mailto:webteam@wes-state.com]
Sent: Thursday, May 27, 2004 9:39 AM
To: postgresql
Subject: [GENERAL] multiple count functions in a select statement

Hello list

I'm a MSSQL convert and I'm running postgresql 7.2

I'm trying to create a statement in which display multiple counts on a table

In MSSQL I would use

select
count_1=(select count(ad_code) AS "CP" from leads where ad_code = '555'),
count_2=(select count(ad_code) AS HED" from leads where ad_code = '222'),
etc

I've searched the docs but to avail.

Any help, suggestions, pointers, would be appreciated

Thanks

Jeannie

Sent a little too quickly the sql should look like this:

select count_1, count_2 from
(select count(*) AS count_1 from leads where ad_code = '555') AS DMY1,
(select count(*) AS count_2 from leads where ad_code = '222') AS DMY2
;

-----Original Message-----
From: Duane Lee - EGOVX
Sent: Tuesday, June 01, 2004 9:54 AM
To: 'Jeannie Stevenson'; postgresql
Subject: RE: [GENERAL] multiple count functions in a select statement

If I understand what you're asking try:

select count_1, count_2 from

(select count(*) AS count_1 from leads where ad_code = '555'),
(select count(*) AS count_2 from leads where ad_code = '222')
;
Duane

-----Original Message-----
From: Jeannie Stevenson [mailto:webteam@wes-state.com]
Sent: Thursday, May 27, 2004 9:39 AM
To: postgresql
Subject: [GENERAL] multiple count functions in a select statement

Hello list

I'm a MSSQL convert and I'm running postgresql 7.2

I'm trying to create a statement in which display multiple counts on a table

In MSSQL I would use

select
count_1=(select count(ad_code) AS "CP" from leads where ad_code = '555'),
count_2=(select count(ad_code) AS HED" from leads where ad_code = '222'),
etc

I've searched the docs but to avail.

Any help, suggestions, pointers, would be appreciated

Thanks

Jeannie