Q: complex SELECT statement

24 Nov 2005 9:43 AM

Petar Popara

If I have table with data:

ID    STATUS
1      1
2      1
3      2
4      3

Can I do this:

SELECT COUNT(STATUS = 1), COUNT(STATUS = 2), COUNT(STATUS = 3) FROM MyTable

or I have to execute select for each status:

SELECT COUNT(ID) FROM MyTable WHERE STATUS = 1
SELECT COUNT(ID) FROM MyTable WHERE STATUS = 2
SELECT COUNT(ID) FROM MyTable WHERE STATUS = 3

and to have three queries?

24 Nov 2005 9:48 AM

Stijn Verrept

Petar Popara wrote:

Have table with data:
Show quote

>
> ID    STATUS
> 1      1
> 2      1
> 3      2
> 4      3
>
> Can I do this:
>
> SELECT COUNT(STATUS = 1), COUNT(STATUS = 2), COUNT(STATUS = 3) FROM
> MyTable
>
> or I have to execute select for each status:
>
> SELECT COUNT(ID) FROM MyTable WHERE STATUS = 1
> SELECT COUNT(ID) FROM MyTable WHERE STATUS = 2
> SELECT COUNT(ID) FROM MyTable WHERE STATUS = 3
>
> and to have three queries?

How about this:

select sum(case when status = 1 then 1 else 0 end) as Count1,
sum(case when status = 2 then 1 else 0 end) as Count2,
sum(case when status = 3 then 1 else 0 end) as Count3 from table

--

HTH,

Stijn Verrept.

24 Nov 2005 10:40 AM

Petar Popara

Thank you.

24 Nov 2005 9:53 AM

Jens

Some other options:

--instead

SELECT COUNT(ID),Status FROM MyTable
GROUP BY STATUS

--OR

SELECT COUNT(STATUS = 1), COUNT(STATUS = 2), COUNT(STATUS = 3) FROM
MyTable

--instad

SELECT SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS STATUS_1,
SUM(CASE WHEN STATUS = 2 THEN 1 ELSE 0 END) AS STATUS_2
SUM(CASE WHEN STATUS = 3 THEN 1 ELSE 0 END) AS STATUS_3
FROM MyTable

HTH, Jens Suessmeyer.

24 Nov 2005 10:39 AM

Roji. P. Thomas

>SELECT COUNT(STATUS = 1), COUNT(STATUS = 2), COUNT(STATUS = 3) FROM
>MyTable

This is incorrect syntax.

My personal favorite is

SELECT COUNT(CASE WHEN STATUS = 1 THEN 'CountMe' END) AS STATUS_1,
COUNT(CASE WHEN STATUS = 2 THEN 'CountMe' END) AS STATUS_2
COUNT(CASE WHEN STATUS = 3 THEN 'CountMe' END) AS STATUS_3
FROM MyTable

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1132825984.327184.151280@g14g2000cwa.googlegroups.com...
> Some other options:
>
>
> --instead
>
> SELECT COUNT(ID),Status FROM MyTable
> GROUP BY STATUS
>
> --OR
>
> SELECT COUNT(STATUS = 1), COUNT(STATUS = 2), COUNT(STATUS = 3) FROM
> MyTable
>
> --instad
>
> SELECT SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS STATUS_1,
> SUM(CASE WHEN STATUS = 2 THEN 1 ELSE 0 END) AS STATUS_2
> SUM(CASE WHEN STATUS = 3 THEN 1 ELSE 0 END) AS STATUS_3
> FROM MyTable
>
>
> HTH, Jens Suessmeyer.
>

24 Nov 2005 10:55 AM

Jens

Thats why I wrote "instead" between the examples :-)

-Jens.

24 Nov 2005 11:44 AM

impslayer

Jens skrev:

> Thats why I wrote "instead" between the examples :-)
>
> -Jens.

I must admit I also got confused by your 'instead' and 'OR' :)

/impslayer, aka Birger Johansson

24 Nov 2005 11:56 AM

Jens

Nice to know will mark that furtheron as <Notworking>Query</notworking>

:-)

Hope it helped anyway, jens.