SQL Select to summarize data and total

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
tmajor99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Are the Code values preset or does the query need to be dynamic?
Dung DinhDBA and Business Intelligence DeveloperCommented:
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,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmajor99Author Commented:
Responding to Brain's question about the codes.  The codes are not known so the query does need to be dynamic.
tmajor99Author Commented:
Is there any other way of doing this by using group by's and cross apply?
Scott PletcherSenior DBACommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.