CODEWITHSUNDEEP
sqlsql-server
Bryan
Bryan

Reputation: 697

Count in SQL statement

I have a table with the following data:

Comp  ID    Name  Type
-----------------------
AAA   D2222 Jon   BR11
AAA   D2222 Jon   BR12
AAA   D2865 Toe   BR11
BBB   D4151 Sue   BR11
BBB   D4151 Sue   BR12
BBB   D4151 Sue   BR13
CCC   D6080 Pete  BR14
CCC   D6723 Tom   BR13

I want to write my SQL statement and display like table below

Comp BR11  BR12  BR13  BR14
---------------------------
AAA   2     1       
BBB   1     1      1    
CCC                1    1

But I only know to select for one Type, how can I do it for many Types ?

select 
    Comp, count(Type) as BR11
from 
    CCDL 
where 
    Type = 'BR11'
group by 
    Comp

Thanks much !

Upvotes: 0

Views: 90

Answers (5)

Akankha Ahmed
Akankha Ahmed

Reputation: 121

What you can do

here 

SELECT Comp, 
    SUM(CASE WHEN type = 'BR11' THEN 1 ELSE 0 END) br11,
    SUM(CASE WHEN type = 'BR12' THEN 1 ELSE 0 END) br12,
    SUM(CASE WHEN type = 'BR13' THEN 1 ELSE 0 END) br13,
    SUM(CASE WHEN type = 'BR14' THEN 1 ELSE 0 END) br14
FROM CCDL
GROUP BY Comp

Upvotes: 1

Shival
Shival

Reputation: 274

try the following code

declare @tab table (Comp varchar(50),Id varchar(50),Name varchar(50),Type varchar(50))
insert into @tab 
Select 'AAA','D2222','Jon','BR11' Union ALL
Select 'AAA','D2222','Jon','BR12' Union ALL
Select 'AAA','D2865','Toe','BR11' Union ALL
Select 'BBB','D4151','Sue','BR11' Union ALL
Select 'BBB','D4151','Sue','BR12' Union ALL
Select 'BBB','D4151','Sue','BR13' Union ALL
Select 'CCC','D6080','Pete','BR14'Union ALL
Select 'CCC','D6723','Tom','BR13'
Select *  from 
(Select  type,comp,count(*) cnt from @tab
group by type,Comp

)d
PIVOT
(Sum(Cnt) FOR Type in ([BR11],[BR12],[BR13],[BR14]))p

Upvotes: 2

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Dynamic pivot is the best approach:

create  table test (Comp varchar(3), ID varchar(10), Name varchar(10), Type varchar(10))

insert into test values ('AAA','D2222','Jon','BR11');            
insert into test values ('AAA','D2222','Jon','BR12');            
insert into test values ('AAA','D2865','Toe','BR11');            
insert into test values ('BBB','D4151','Sue','BR11');            
insert into test values ('BBB','D4151','Sue','BR12');            
insert into test values ('BBB','D4151','Sue','BR13');            
insert into test values ('CCC','D6080','Pete','BR14');           
insert into test values ('CCC','D6723','Tom','BR13');            



DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Type) 
            FROM test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Comp, ' + @cols + ' from 
            (
                select Comp
                    , ID
                    , Type
                from test
           ) x
            pivot 
            (
                 count(ID)
                for Type in (' + @cols + ')
            ) p '


execute(@query)

The result is 

Comp    BR11    BR12    BR13    BR14
AAA       2       1       0      0
BBB       1       1       1      0 
CCC       0       0       1      1

Upvotes: 3

Markus Winand
Markus Winand

Reputation: 8706

You can use selective aggregates for this:

SELECT Comp
     , COUNT(CASE WHEN type = 'BR11' THEN 1 END) br11
     , COUNT(CASE WHEN type = 'BR12' THEN 1 END) br12
     ,  ...
  FROM CCDL
 GROUP BY Comp

More about this: http://modern-sql.com/feature/filter

It's basically also a pivot technique: http://modern-sql.com/use-case/pivot

Upvotes: 2

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS : Use CASE with SUM as below:

SELECT Comp, 
    SUM(CASE WHEN type = 'BR11' THEN 1 ELSE 0 END) br11,
    SUM(CASE WHEN type = 'BR12' THEN 1 ELSE 0 END) br12,
    SUM(CASE WHEN type = 'BR13' THEN 1 ELSE 0 END) br13,
    SUM(CASE WHEN type = 'BR14' THEN 1 ELSE 0 END) br14
FROM CCDL
GROUP BY Comp

Upvotes: 1

Related Questions