none
Combining conditional COUNTs in one GROUP BY query

    Question

  • Hello,

    I have a table that I want produce a report query for.  Here's the table:

    ID Name Device Upgraded
    1 Jones phone 1
    2 Jones computer 1
    3 Jones TV 0
    4 Jones fax 0
    5 Smith phone 0
    6 Smith computer 1
    7 Smith TV 0
    8 Smith fax 0
    9 Brown phone 0
    10 Brown computer 1
    11 Brown TV 1
    12 Brown fax 1


    I wrote one query that counts the number of Devices for each Name:

     

    SELECT Name, COUNT(Name) AS 'Total Devices' 
    GROUP BY Name

     

    This gives me the result:

    Name Total Devices
    Jones 4
    Smith 4
    Brown 4


    I wrote another query that counts the number of  Upgrades:

     

    SELECT Name, COUNT(Upgraded) as 'Total Upgrades' 
    WHERE Upgrade = '1'
    GROUP BY Name

     

    This gives me the result:

    Name Total Upgrades
    Jones 2
    Smith 1
    Brown 3

    What I want to do is combine these numbers in one query result:

    Name Total Devices Total Upgrades
    Jones 4 2
    Smith 4 1
    Brown 4 3

    How can I do this in one SQL statement?

    Thanks!
    Bill J




    Friday, March 18, 2011 9:03 PM

Answers

  • Hope this will help:
    
    Declare @myTable Table (ID Int, Name Varchar(10), Device Varchar(10), Upgraded TinyInt)
    INsert Into @myTable 
    Select 1, 'Jones', 'phone', 1 Union All 
    Select 2, 'Jones', 'computer', 1 Union All 
    Select 3, 'Jones', 'TV', 0 Union All 
    Select 4, 'Jones', 'fax', 0 Union All 
    Select 5, 'Smith', 'phone', 0 Union All 
    Select 6, 'Smith', 'computer', 1 Union All 
    Select 7, 'Smith', 'TV', 0 Union All 
    Select 8, 'Smith', 'fax', 0 Union All 
    Select 9, 'Brown', 'phone', 0 Union All 
    Select 10, 'Brown', 'computer', 1 Union All 
    Select 11, 'Brown', 'TV', 1 Union All 
    Select 12, 'Brown', 'fax', 1 
    
    Select 
    	Name , 
    	COUNT(Name) As CountName, 
    	Sum(Case When Upgraded = 1 Then 1 Else 0 End) As CountUpgraded
    From 
    	@myTable 
    Group By 
    	Name 
    

    And output:

    Name CountName CountUpgraded

    ---------- ----------- -------------

    Brown 4 3

    Jones 4 2

    Smith 4 1


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Gert-Jan Strik Saturday, March 19, 2011 11:56 AM
    • Marked as answer by Bill in MI Monday, March 21, 2011 12:56 PM
    Friday, March 18, 2011 9:17 PM
  • Would following work for you?

    SELECT Name, COUNT(device) as totaldevice, SUM( case when upgraded = 1 then 1 else 0 end) as totalupgrades
    
    
     FROM <table> Group By Name
    
    

    • Marked as answer by Bill in MI Monday, March 21, 2011 12:56 PM
    Friday, March 18, 2011 9:17 PM

All replies

  • Hope this will help:
    
    Declare @myTable Table (ID Int, Name Varchar(10), Device Varchar(10), Upgraded TinyInt)
    INsert Into @myTable 
    Select 1, 'Jones', 'phone', 1 Union All 
    Select 2, 'Jones', 'computer', 1 Union All 
    Select 3, 'Jones', 'TV', 0 Union All 
    Select 4, 'Jones', 'fax', 0 Union All 
    Select 5, 'Smith', 'phone', 0 Union All 
    Select 6, 'Smith', 'computer', 1 Union All 
    Select 7, 'Smith', 'TV', 0 Union All 
    Select 8, 'Smith', 'fax', 0 Union All 
    Select 9, 'Brown', 'phone', 0 Union All 
    Select 10, 'Brown', 'computer', 1 Union All 
    Select 11, 'Brown', 'TV', 1 Union All 
    Select 12, 'Brown', 'fax', 1 
    
    Select 
    	Name , 
    	COUNT(Name) As CountName, 
    	Sum(Case When Upgraded = 1 Then 1 Else 0 End) As CountUpgraded
    From 
    	@myTable 
    Group By 
    	Name 
    

    And output:

    Name CountName CountUpgraded

    ---------- ----------- -------------

    Brown 4 3

    Jones 4 2

    Smith 4 1


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Gert-Jan Strik Saturday, March 19, 2011 11:56 AM
    • Marked as answer by Bill in MI Monday, March 21, 2011 12:56 PM
    Friday, March 18, 2011 9:17 PM
  • Would following work for you?

    SELECT Name, COUNT(device) as totaldevice, SUM( case when upgraded = 1 then 1 else 0 end) as totalupgrades
    
    
     FROM <table> Group By Name
    
    

    • Marked as answer by Bill in MI Monday, March 21, 2011 12:56 PM
    Friday, March 18, 2011 9:17 PM
  • Arbi & Chirag,

    Thanks to both of you.  The SUM CASE WHEN statement is what I needed and it works great.

    Cheers,

    Bill J

    Monday, March 21, 2011 12:57 PM