December 16, 2009 at 8:18 pm
Hello,
I have a table with a column name "Result". This column contains 5 possible values : A, B, C, D or E.
I need a query that will count each different value.
Example :
Row 1 Result = C
Row 2 Result = A
Row 3 Result = B
Row 4 Result = B
Row 5 Result = D
Row 6 Result = E
Row 7 Result = A
Row 8 Result = B
Row 9 Result = C
Row 10 Result = E
The query shoud return
CountofA CountofB CountofC CountofD CountofE
--------- -------- --------- --------- ---------
2 3 2 1 2
Can do this in a single query?
thanks
Martin
December 16, 2009 at 8:46 pm
create table #temp
(
slno int identity(1,1),
name1 varchar(2)
)
insert into #temp (name1)
select 'C'
union all
select 'C'
union all
select 'A'
union all
select 'B'
union all
select 'B'
union all
select 'C'
union all
select 'D'
union all
select 'E'
select
max(case when a.name1 = 'A' and a.name1 = b.name1 then b.COUNT1 else ''end) ACOUNT,
max(case when a.name1 = 'B' and a.name1 = b.name1 then b.COUNT1 else ''end) BCOUNT,
max(case when a.name1 = 'C' and a.name1 = b.name1 then b.COUNT1 else ''end) CCOUNT,
max(case when a.name1 = 'D' and a.name1 = b.name1 then b.COUNT1 else ''end) DCOUNT,
max(case when a.name1 = 'E' and a.name1 = b.name1 then b.COUNT1 else ''end) ECOUNT
from #temp a inner join
(select name1,count(name1)COUNT1 from #temp
group by name1)b
on a.name1 = b.name1
December 17, 2009 at 12:25 am
On SQL Sever 2005 you can use PIVOT operator whch is for such purposes.
DECLARE @temp TABLE
(
RowNo int,
Result char(1)
)
INSERT INTO @temp (RowNo, Result)
SELECT 1, 'C' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 3, 'B' UNION ALL
SELECT 4, 'B' UNION ALL
SELECT 5, 'D' UNION ALL
SELECT 6, 'E' UNION ALL
SELECT 7, 'A' UNION ALL
SELECT 8, 'B' UNION ALL
SELECT 9, 'C' UNION ALL
SELECT 10, 'E'
SELECT
[A] AS CountA,
AS CountB,
[C] AS CountC,
[D] AS CountD,
[E] AS CountE
FROM
(
SELECT
RowNo,
Result
FROM @temp
) p
PIVOT
(
COUNT(RowNo)
FOR Result IN ([A], , [C], [D], [E])
) pvt
or you can use a simple select for this.
SELECT
SUM(CASE WHEN Result = 'A' THEN 1 ELSE 0 END) AS CountA,
SUM(CASE WHEN Result = 'B' THEN 1 ELSE 0 END) AS CountB,
SUM(CASE WHEN Result = 'C' THEN 1 ELSE 0 END) AS CountC,
SUM(CASE WHEN Result = 'D' THEN 1 ELSE 0 END) AS CountD,
SUM(CASE WHEN Result = 'E' THEN 1 ELSE 0 END) AS CountE
FROM @temp
December 17, 2009 at 7:27 am
PIVOT is cool!
Is the PIVOT query will perform better than the second quety with multiple SUM ?
Martin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply