Display total qty sold of each item of each store? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Display total qty sold of each item of each store?

Author

Topic

dchencm
Starting Member

6 Posts

Posted - 2014-06-27 : 15:44:57

Hi,

I have two tables:

table 1 - ItemMaster:
- Item#

table 2 - ItemMovement
- Store#
- Item#
- QtySold

In order to generate a table showing the totals of each item been sold at each store, could you teach me how to generate this kind of SQL query in order to display a table like the example below?

Item # | Sale Total of All Stores | Store A | Store B | Store C
---------------------------------------------------------------------
item 1 | 45 | 10 | 15 | 20
item 2 | 60 | 20 | 10 | 30
item 3 | 50 | 15 | 25 | 10
---------------------------------------------------------------------
TOTAL | 155 | 45 | 50 | 60

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-27 : 16:05:18

[code]SELECT
a.[Item#],
SUM(QtySold) AS [Sale Total],
SUM( CASE WHEN b.[Store#] = 'Store A' THEN b.QtySold ELSE 0 END) AS [Store A],
SUM( CASE WHEN b.[Store#] = 'Store B' THEN b.QtySold ELSE 0 END) AS [Store B]
--- etc
FROM
ItemMaster a
LEFT JOIN ItemMovement b ON
a.[Item#] = b.[Item#]
GROUP BY
a.[Item#][/code]

dchencm
Starting Member

6 Posts

Posted - 2014-06-27 : 16:35:21

Thank you, James.

But how do I display the grand total (last row)?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-27 : 16:47:28

Change the group by clause to

GROUP BY GROUPING SETS
   ( (a.[Item#]),());

dchencm
Starting Member

6 Posts

Posted - 2014-06-28 : 13:27:01

Thank you, James.
It seems to be working except if the value is zero, it doesn't display the rows that are zero.
I have tried LEFT OUTER JOIN and FULL OUTER JOIN by didn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-28 : 13:49:23

Works for me too

DECLARE	@ItemMaster TABLE
	(
		Item VARCHAR(10) PRIMARY KEY CLUSTERED
	);

INSERT	@ItemMaster
	(
		Item
	)
VALUES	('Item 1'),
	('Item 2'),
	('Item 3'),
	('Item 4');

DECLARE	@ItemMovement TABLE
	(
		Store VARCHAR(10) NOT NULL,
		Item VARCHAR(10) NOT NULL,
		QtySold INT NOT NULL
	);

INSERT	@ItemMovement
	(
		Store,
		Item,
		QtySold
	)
VALUES	('Store A', 'Item 1', 10),
	('Store A', 'Item 2', 20),
	('Store A', 'Item 3', 15),
	('Store B', 'Item 1', 15),
	('Store B', 'Item 2', 10),
	('Store B', 'Item 3', 25),
	('Store C', 'Item 1', 20),
	('Store C', 'Item 2', 30),
	('Store C', 'Item 3', 10);

-- SwePeso
SELECT		ISNULL(i.Item, 'TOTAL') AS [Item #],
		SUM(ISNULL(m.QtySold, 0)) AS [Sale Total of All Stores],
		SUM(CASE WHEN m.Store = 'Store A' THEN m.QtySold ELSE 0 END) AS [Store A],
		SUM(CASE WHEN m.Store = 'Store B' THEN m.QtySold ELSE 0 END) AS [Store B],
		SUM(CASE WHEN m.Store = 'Store C' THEN m.QtySold ELSE 0 END) AS [Store C]
FROM		@ItemMaster AS i
LEFT JOIN	@ItemMovement AS m ON m.Item = i.Item
GROUP BY	GROUPING SETS
		(
			(i.Item),
			()
		);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

dchencm
Starting Member

6 Posts

Posted - 2014-06-28 : 21:04:02

This is my actual testing, but the zero values still don't show up.

SELECT
--1.UPC
[UPC] =
(
CASE WHEN [UPC] IS NULL THEN 'Grand Total'
ELSE [UPC]
END
)

--2.ItemCode
--,[ItemCode] AS [Item Code]
, CASE WHEN [ItemCode] IS NULL THEN ''
ELSE [ItemCode]
END AS [Item Code]

--3.Vendor
--,[Vendor] AS [Vendor#]
,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]

--4.MixMatch
, CASE WHEN [MixMatch] IS NULL THEN ''
ELSE [MixMatch]
END AS [Mix Match]

--5.Description
, CASE WHEN [Description] IS NULL THEN ''
ELSE [Description]
END AS [Description]

--6.CaseSize
,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]

--7.Size
,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]

--8.Measure
, CASE WHEN [Measure] IS NULL THEN ''
ELSE [Measure]
END AS [Measure]

--9.Date Listed
,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]

--10.Case Cost
,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]

--11.Unit Cost
,ISNULL(CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]

--12.Regular Retail
,ISNULL(CONVERT(varchar(20),[RegularRetail]),'') [Regular(Unit) Retail]

--13.Margin
--,CONVERT(decimal(20,1), (100 * (1 - [UnitCost] / [RegularRetail]))) AS [Margin(%)]
,ISNULL(CONVERT(varchar(20),CONVERT(decimal(20,1), (100 * (1 - [UnitCost] / [RegularRetail])))),'') [Margin(%)]

--14.
,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count (# Items Sold)]

--15. 902-16
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '1' THEN [QtySold] ELSE 0 END),0)) AS [902-16]

--15. 906-CA
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '2' THEN [QtySold] ELSE 0 END),0)) AS [906-CA]

--15. 908-57
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '3' THEN [QtySold] ELSE 0 END),0)) AS [908-57]

--15. 907-YT
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '4' THEN [QtySold] ELSE 0 END),0)) AS [907-YT]

--15. 787-WR
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '6' THEN [QtySold] ELSE 0 END),0)) AS [787-WR]

--15. 789-CR
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '7' THEN [QtySold] ELSE 0 END),0)) AS [789-CR]

--15. 788-KA
,CONVERT(int,ROUND(SUM( CASE WHEN [StoreNumber] = '8' THEN [QtySold] ELSE 0 END),0)) AS [788-KA]

--16.
,CONVERT(decimal(20,2), SUM([TotalCost])) AS [Total Cost of Items Sold]

FROM [JS_Data].[dbo].[View_GrandTotal_count_of_eachStore_v1]
WHERE (UPC BETWEEN '82983500000' AND '82983599999')
AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
GROUP BY
GROUPING SETS
(
(
[UPC]
,[ItemCode]
,[Vendor]
,[MixMatch]
,[Description]
,[CaseSize]
,[Size]
,[Measure]
,[CreationDate]
,[CaseCost]
,[UnitCost]
,[RegularRetail]
)
,()
);

*************************************
My View is set in LEFT OUTER JOIN.
SELECT dbo.ItemMaster.UPC, dbo.ItemMovement.StoreNumber, dbo.ItemMovement.QtySold, dbo.ItemMovement.SaleDate, dbo.ItemMaster.Description, dbo.ItemMaster.Size,
dbo.ItemMaster.ItemCode, dbo.ItemMaster.Vendor, dbo.ItemMaster.CaseSize, dbo.ItemMaster.Measure, dbo.ItemMaster.MixMatch, dbo.ItemMaster.CreationDate,
dbo.ItemMaster.CaseCost, dbo.ItemMovement.UnitCost, dbo.ItemMaster.RegularRetail, dbo.ItemMovement.TotalCost
FROM dbo.ItemMaster LEFT OUTER JOIN
dbo.ItemMovement ON dbo.ItemMaster.UPC = dbo.ItemMovement.UPC

dchencm
Starting Member

6 Posts

Posted - 2014-06-29 : 01:10:37

i think i found the reason.
it was because the dates need to be ISNULL

thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources