-
Query Help
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.
-
Code:
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
You can use CASE
You can use the case syntax in sql server. Try this...
Code:
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|