Query Help

[kkruk]

Hello all

I am having a problem and I hope you guys could help me with it. Essentially, I have to create a query. This essentially what my db tables look like.


----------------
| Name | Status|
----------------
|First | 1 |
|First | 2 |
|Second| 3 |
|Second| 2 |
|Third | 1 |
----------------


From this table, I have to get the following table:

--------------------------------------------
|Name |Count Total|Count 1|Count 2|Count 3|
--------------------------------------------
|First | 2 | 1 | 1 | 0 |
|Second| 2 | 0 | 1 | 1 |
|Third | 1 | 1 | 0 | 0 |
--------------------------------------------

Here, "Count Total" is the total number of entries for each name, "Count 1" is the total number of entries with the Status 1 for each name, "Count 2" is for status 2, and so on.


I have a feeling that this quiry can be done fairly easily, but I cannot envision it. Any help you guys could give would be great.

Thank you in advance.

[jmodic]

select
name,
count(*) as count_total,
count(decode(status, 1, 1)) as count_1,
count(decode(status, 2, 1)) as count_2,
count(decode(status, 3, 1)) as count_3
from my_table
group by name;


You can also use CASE expression instead of DECODE.

[kkruk]

Thanks

However, and this is my fault for not mentioning, but I am using SQL server, :) (yea, I know, wrong forum) and it does not seem to have the decode function. I tried replacing it with "case" but I don't think it is just that simple since I am getting a different error.

Do you know this could work for SQL Server?

[wdfloyd]

You can use the case syntax in sql server. Try this...


select
name,
count(*) as count_total,
count(case status when 1 then 1 end) as count_1,
count(case status when 2 then 1 end) as count_2,
count(case status when 3 then 1 end) as count_3
from my_table
group by name;