Display Totals of a table

Debilon 431 Reputation points
2023-02-17T15:02:09.1066667+00:00

I need to find totals of a table and create a report
If a rating is Y it counts as 1
if all ratings are N is counts as UnDesignated.

DECLARE @T AS TABLE  
    (  
        id int identity(1,1),  
		BName			Varchar (50),
		Adress1			Varchar(50),
        City		    Varchar(10),
		BState			Varchar(2),
		ARatingClass1	Varchar(1),
		ARatingClass2   Varchar(1),
		ARatingClass3   Varchar(1),
		PRatingClass1	Varchar(1),
		PRatingClass2	Varchar(1),
		PRatingClass3	Varchar(1),
		IRatingClass1   Varchar(1),
		IRatingClass2   Varchar(1),
		IRatingClass3   Varchar(1)


	
    )  
      
    INSERT INTO @T VALUES  
    ('Alpha','128 Alpha Street','New York','NY',     'N','Y','N','Y','Y','Y','N','Y','Y'),
	('Bravo','126 Bravo Street','Van Nuys','CA',     'Y','N','Y','N','N','n','y','N','N'),
	('Charlie','124 Charlie Street','Chicago','IL',  'Y','N','Y','N','N','n','y','N','N'),
	('Delta','121 Delta Street','Miami','FL',        'N','N','N','N','N','N','N','N','N'),
	('Echo','119 Echo Street','Augusta','GA',        'N','N','N','N','N','N','N','N','N')
...

Totals:
	ARatingClass1: 2	
	ARatingClass2: 1 
	ARatingClass3: 2 
	PRatingClass1: 1	
	PRatingClass2: 1	
	PRatingClass3: 1	
	IRatingClass1: 2 
	IRatingClass2: 1 
	IRatingClass3: 1
	UnDesignated:  2


SQL Server | Other
{count} votes

5 answers

Sort by: Most helpful
  1. Haris Rashid 81 Reputation points
    2023-02-17T15:35:27.9266667+00:00

    This can be accomplished using pivot function. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. See pivot function at: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

    0 comments No comments

  2. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2023-02-17T15:38:58.3833333+00:00

    Here is the unpivot part:

    
    DECLARE @T AS TABLE  
        (  
            id int identity(1,1),  
    		BName			Varchar (50),
    		Adress1			Varchar(50),
            City		    Varchar(10),
    		BState			Varchar(2),
    		ARatingClass1	Varchar(1),
    		ARatingClass2   Varchar(1),
    		ARatingClass3   Varchar(1),
    		PRatingClass1	Varchar(1),
    		PRatingClass2	Varchar(1),
    		PRatingClass3	Varchar(1),
    		IRatingClass1   Varchar(1),
    		IRatingClass2   Varchar(1),
    		IRatingClass3   Varchar(1)
    
    
    	
        )  
          
        INSERT INTO @T VALUES  
        ('Alpha','128 Alpha Street','New York','NY',     'N','Y','N','Y','Y','Y','N','Y','Y'),
    	('Bravo','126 Bravo Street','Van Nuys','CA',     'Y','N','Y','N','N','n','y','N','N'),
    	('Charlie','124 Charlie Street','Chicago','IL',  'Y','N','Y','N','N','n','y','N','N'),
    	('Delta','121 Delta Street','Miami','FL',        'N','N','N','N','N','N','N','N','N'),
    	('Echo','119 Echo Street','Augusta','GA',        'N','N','N','N','N','N','N','N','N')
    --...
    
    --Totals:
    --	ARatingClass1: 2	
    --	ARatingClass2: 1 
    --	ARatingClass3: 2 
    --	PRatingClass1: 1	
    --	PRatingClass2: 1	
    --	PRatingClass3: 1	
    --	IRatingClass1: 2 
    --	IRatingClass2: 1 
    --	IRatingClass3: 1
    --	UnDesignated:  2
    
    select Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t
      t
    CROSS APPLY (Values ('ARatingClass1', ARatingClass1),
    		('ARatingClass2', ARatingClass2),
    		('ARatingClass3', ARatingClass3),
    		('PRatingClass1', PRatingClass1),
    		('PRatingClass2', PRatingClass2),
    		('PRatingClass3', PRatingClass3),
    		('IRatingClass1',IRatingClass1),
    		('IRatingClass2',IRatingClass2),
    		('IRatingClass3',IRatingClass3))  d(Col,val) 
    Group by Col
    
    --Need more information for UnDesignated
    

  3. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2023-02-17T15:52:36.73+00:00

    the whole query:

    
    
    DECLARE @T AS TABLE  
        (  
            id int identity(1,1),  
    		BName			Varchar (50),
    		Adress1			Varchar(50),
            City		    Varchar(10),
    		BState			Varchar(2),
    		ARatingClass1	Varchar(1),
    		ARatingClass2   Varchar(1),
    		ARatingClass3   Varchar(1),
    		PRatingClass1	Varchar(1),
    		PRatingClass2	Varchar(1),
    		PRatingClass3	Varchar(1),
    		IRatingClass1   Varchar(1),
    		IRatingClass2   Varchar(1),
    		IRatingClass3   Varchar(1)
    
    
    	
        )  
          
        INSERT INTO @T VALUES  
        ('Alpha','128 Alpha Street','New York','NY',     'N','Y','N','Y','Y','Y','N','Y','Y'),
    	('Bravo','126 Bravo Street','Van Nuys','CA',     'Y','N','Y','N','N','n','y','N','N'),
    	('Charlie','124 Charlie Street','Chicago','IL',  'Y','N','Y','N','N','n','y','N','N'),
    	('Delta','121 Delta Street','Miami','FL',        'N','N','N','N','N','N','N','N','N'),
    	('Echo','119 Echo Street','Augusta','GA',        'N','N','N','N','N','N','N','N','N')
     
     
    ;with mycte as (
    select BName,Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t
      t
    CROSS APPLY (Values ('ARatingClass1', ARatingClass1),
    		('ARatingClass2', ARatingClass2),
    		('ARatingClass3', ARatingClass3),
    		('PRatingClass1', PRatingClass1),
    		('PRatingClass2', PRatingClass2),
    		('PRatingClass3', PRatingClass3),
    		('IRatingClass1',IRatingClass1),
    		('IRatingClass2',IRatingClass2),
    		('IRatingClass3',IRatingClass3))  d(Col,val) 
    Group by Col,BName)
    ,cteUnDesignated as (
    select 'UnDesignated'   Col, count(distinct BName) cnt
    from mycte
    group by BName
     Having(sum(cnt)=0)
     )
    
    
     select Col, Sum(cnt) cnt from mycte
      group by Col
     UNION ALL
      select Col,sum(cnt) cnt from cteUnDesignated
     group by Col
     
    
    
    --Need more information for UnDesignated
    

  4. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2023-02-17T19:19:40.9633333+00:00
    
    DECLARE @T AS TABLE  
        (  
            id int identity(1,1),  
    		BName			Varchar (50),
    		Adress1			Varchar(50),
            City		    Varchar(10),
    		BState			Varchar(2),
    		ARatingClass1	Varchar(1),
    		ARatingClass2   Varchar(1),
    		ARatingClass3   Varchar(1),
    		PRatingClass1	Varchar(1),
    		PRatingClass2	Varchar(1),
    		PRatingClass3	Varchar(1),
    		IRatingClass1   Varchar(1),
    		IRatingClass2   Varchar(1),
    		IRatingClass3   Varchar(1)
    
    
    	
        )  
          
        INSERT INTO @T VALUES  
        ('Alpha','128 Alpha Street','New York','NY',     'N','Y','N','Y','Y','Y','N','Y','Y'),
    	('Bravo','126 Bravo Street','Van Nuys','CA',     'Y','N','Y','N','N','n','y','N','N'),
    	('Charlie','124 Charlie Street','Chicago','IL',  'Y','N','Y','N','N','n','y','N','N'),
    	('Delta','121 Delta Street','Miami','FL',        'N','N','N','N','N','N','N','N','N'),
    	('Echo','119 Echo Street','Augusta','GA',        'N','N','N','N','N','N','N','N','N')
     
     
    ;with mycte as (
    select BName,Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t
      t
    CROSS APPLY (Values ('ARatingClass1', ARatingClass1),
    		('ARatingClass2', ARatingClass2),
    		('ARatingClass3', ARatingClass3),
    		('PRatingClass1', PRatingClass1),
    		('PRatingClass2', PRatingClass2),
    		('PRatingClass3', PRatingClass3),
    		('IRatingClass1',IRatingClass1),
    		('IRatingClass2',IRatingClass2),
    		('IRatingClass3',IRatingClass3))  d(Col,val) 
    Group by Col,BName)
    ,cteUnDesignated as (
    select 'UnDesignated'   Col, count(distinct BName) cnt
    from mycte
    group by BName
     Having(sum(cnt)=0)
     )
    
     ,mycteFinal as
     (
     select Col, Sum(cnt) cnt from mycte
      group by Col
     UNION ALL
      select Col,sum(cnt) cnt from cteUnDesignated
     group by Col
     )
     
     
    ,myPivot as (
     select Col, cnt ,row_number()Over(partition by left(Col,10)  Order by Right(Col,1) )
     rn from mycteFinal)
    
     Select 
     max(Case when rn=1 then Col else null end) col1,
      max(Case when rn=1 then cnt else null end) cnt1,
      max(Case when rn=2 then Col else null end) col2,
      max(Case when rn=2 then cnt else null end) cnt2, 
      max(Case when rn=3 then Col else null end) col3,
      max(Case when rn=3 then cnt else null end) cnt3 
      from mypivot
     group by  left(Col,10)
     
     
    

  5. Anonymous
    2023-02-20T06:29:29.03+00:00

    Hi @Debilon

    In response to your initial question, I wrote the following query.

    DECLARE @T AS TABLE  
           (id int identity(1,1),  
    		BName			Varchar (50),
    		Adress1			Varchar(50),
            City		    Varchar(10),
    		BState			Varchar(2),
    		ARatingClass1	Varchar(1),
    		ARatingClass2   Varchar(1),
    		ARatingClass3   Varchar(1),
    		PRatingClass1	Varchar(1),
    		PRatingClass2	Varchar(1),
    		PRatingClass3	Varchar(1),
    		IRatingClass1   Varchar(1),
    		IRatingClass2   Varchar(1),
    		IRatingClass3   Varchar(1) )   
        INSERT INTO @T VALUES  
        ('Alpha','128 Alpha Street','New York','NY',     'N','Y','N','Y','Y','Y','N','Y','Y'),
    	('Bravo','126 Bravo Street','Van Nuys','CA',     'Y','N','Y','N','N','n','y','N','N'),
    	('Charlie','124 Charlie Street','Chicago','IL',  'Y','N','Y','N','N','n','y','N','N'),
    	('Delta','121 Delta Street','Miami','FL',        'N','N','N','N','N','N','N','N','N'),
    	('Echo','119 Echo Street','Augusta','GA',        'N','N','N','N','N','N','N','N','N');
    with T1 as(
      select case when ARatingClass1 = 'Y' then 1 else 0 end as ARatingClass1, 
           case when ARatingClass2 = 'Y' then 1 else 0 end as ARatingClass2,
    	   case when ARatingClass3 = 'Y' then 1 else 0 end as ARatingClass3,
    	   case when PRatingClass1 = 'Y' then 1 else 0 end as PRatingClass1,
    	   case when PRatingClass2 = 'Y' then 1 else 0 end as PRatingClass2,
    	   case when PRatingClass3 = 'Y' then 1 else 0 end as PRatingClass3,
    	   case when IRatingClass1 = 'Y' then 1 else 0 end as IRatingClass1,
    	   case when IRatingClass2 = 'Y' then 1 else 0 end as IRatingClass2,
    	   case when IRatingClass3 = 'Y' then 1 else 0 end as IRatingClass3 from @T
    ),T2 as(
      select *,ARatingClass1 + ARatingClass2 + ARatingClass3  
             + PRatingClass1 + PRatingClass2 + PRatingClass3 
    		 + IRatingClass1 + IRatingClass2 + IRatingClass3 as uns from T1
    ),T3 as(
      select ARatingClass1,ARatingClass2,ARatingClass3  
             ,PRatingClass1,PRatingClass2,PRatingClass3 
    		 ,IRatingClass1,IRatingClass2,IRatingClass3,
    		 case when uns = 0 then 1 else 0 end as uns from T2
    ),T4 as(
      select sum(ARatingClass1) as ARatingClass1,sum(ARatingClass2) as ARatingClass2
           ,sum(ARatingClass3) as ARatingClass3,sum(PRatingClass1) as PRatingClass1,
    	   sum(PRatingClass2) as PRatingClass2,sum(PRatingClass3) as PRatingClass3,
    	   sum(IRatingClass1) as IRatingClass1,sum(IRatingClass2) as IRatingClass2,
    	   sum(IRatingClass3) as IRatingClass3,sum(uns) as UnDesignated from T3
    ),T5 as(
    select * from T4 as t
    	   unpivot(nums for kinds in(ARatingClass1,ARatingClass2,ARatingClass3,
    	   PRatingClass1,PRatingClass2,PRatingClass3,IRatingClass1,IRatingClass2,
    	   IRatingClass3,UnDesignated)) as d)
    select kinds,nums from T5;
    
    

    But when I finished writing and looked at your new comments, I realized that your real problem was much more complicated. The list of categories is so long, it's hard to imagine how complex a query would be if you just used it. Maybe you can consider Jingyang Li's suggestion.

    Best regards,

    Percy Tang

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.