SQL Select to summarize data and total

tmajor99
tmajor99 used Ask the Experts™
on
I need a SQL Select that will summarize data by category and code.  Here is what my table rows look like:

Product     Code        Category    
1203           O              Online          
3900           H              Tools          
4999           X               Heaters      
5009           O              Heaters      
8990                            Tools          
3111           H              Tools          
5115           O              Tools            

I need a SQL select that will summarize the data by Category and Code like this:
                           O             X            H       Other         Total    
Online               1                                                          1
Tools                 1                             2          1               4
Heaters            1               1                                          2

Summary         3              1            2          1                7
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
Are the Code values preset or does the query need to be dynamic?
DBA and Business Intelligence Developer
Commented:
I assumed that your category is consistent with O,X,H and Other.  Try  with this
USE Demo
GO
IF OBJECT_ID(N'Product') IS NOT NULL
   DROP TABLE Product
GO
CREATE TABLE Product(ProductID int ,Code nvarchar(30),Category nvarchar(50))
GO
INSERT INTO Product
VALUES(1203,'O','Online'),
(3900,'H','Tools'),
(4999,'X','Heaters'),
(5009,'O','Heaters'),
(8990,'Other','Tools'),
(3111,'H','Tools'),
(5115,'O','Tools')
GO

IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL
   DROP TABLE tempdb..#tmp
GO
SELECT Category,ISNULL(Code,'Other') AS Code ,COUNT(*) AS TotalByCode
INTO #tmp
FROM Product
GROUP BY Category,Code

;WITH r
AS
(
SELECT [Category],[O],[X],[H],[Other]
FROM #tmp
	PIVOT
	(
	  SUM(TotalByCode)
	  FOR Code IN ([O],[X],[H],[Other])
	) AS piv
)
SELECT r.Category,ISNULL(r.O,0) [O],ISNULL(r.X,0) [X],ISNULL(r.H,0) [H],ISNULL(r.Other,0) [Other]
,(ISNULL(r.O,0)+ISNULL(r.X,0)+ISNULL(r.H,0)+ISNULL(r.Other,0)) As Total
FROM r
UNION ALL
SELECT 'Summary',SUM([O]),SUM([X]),SUM([H]),SUM([Other]),(SUM([O])+SUM([X])+SUM([H])+SUM([Other])) 
FROM r;

Open in new window


In case, you don't know what categories, let's try to use dynamic query
DECLARE @ProductCode1 varchar(8000), 
                @ProductCode2 varchar(8000),
		@SubByCat varchar(8000),
		@SubByCode varchar(8000),
		@TotalSummary varchar(8000),

        @strQuery varchar(8000);
SET @ProductCode1 =SUBSTRING(( SELECT DISTINCT ',['+Code+']' 
FROM Product FOR XML PATH('')),2,8000)

SET @ProductCode2 =SUBSTRING(( SELECT DISTINCT ',IsNull(['+Code+'],0) as ['+Code+']' 
FROM Product FOR XML path('')),2,8000)

SET @SubByCat =SUBSTRING(( SELECT DISTINCT '+IsNull(['+Code+'],0)' 
FROM Product FOR XML path('')),2,8000)

SET @TotalSummary = SUBSTRING(( SELECT DISTINCT '+SUM(IsNull(['+Code+'],0))' 
FROM Product FOR XML path('')),2,8000)


SET @SubByCode = SUBSTRING(( SELECT DISTINCT ',SUM(IsNull(['+Code+'],0))' 
FROM Product FOR XML path('')),2,8000)


SELECT @ProductCode1,@ProductCode2,@SubByCat,@SubByCode,@TotalSummary

SET @strQuery ='
WITH r
AS
(
	SELECT Category, '+@ProductCode2+' 
	FROM
	(
		SELECT Category , Code , COUNT(*) AS Total FROM Product
		GROUP BY Category , Code
	) AS s
	PIVOT
	(
	      SUM(Total) FOR Code IN ('+@ProductCode1+')
	) AS pv
) 
SELECT Category,'+@ProductCode2+',('+@SubByCat+') As Total 
FROM r
UNION ALL
SELECT ''Summary'','+@SubByCode+',('+@TotalSummary+')
FROM r;'

PRINT (@strQuery)
EXEC  (@strQuery)

Open in new window


The purpose I tried to get list of categories at run-time and built a dynamic query to bind the list into PIVOT statement.

Thanks,

Author

Commented:
Responding to Brain's question about the codes.  The codes are not known so the query does need to be dynamic.

Author

Commented:
Is there any other way of doing this by using group by's and cross apply?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Code below will generate a "cross tab" query using SUM(CASE ...) and GROUP BY.  I'm not sure how you intended "CROSS APPLY" to be used here.


DECLARE @sqlwork varchar(max)
DECLARE @sql varchar(max)

SELECT @sqlwork = (
    SELECT 'SUM(CASE WHEN code = ''' + code + ''' THEN 1 ELSE 0 END) AS [' + code + '],'
    FROM (
        SELECT DISTINCT code
        FROM table_name
        WHERE code > ''
    ) AS distinct_codes
    ORDER BY code
    FOR XML PATH(''))

SET @sqlwork = @sqlwork + 'SUM(CASE WHEN code IS NULL OR code = '''' THEN 1 ELSE 0 END) AS [Other],' +
    'SUM(1) AS [Total]'

SET @sql = 'SELECT category,' + @sqlwork + ' FROM table_name GROUP BY category'

SET @sql = @sql + ' UNION ALL ' + 'SELECT ''' + CHAR(254) + 'Summary'', ' + @sqlwork + ' FROM table_name ORDER BY category'

PRINT @sql

EXEC(@sql)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial