| 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 tooDECLARE @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. |
|
|
|
|
|
|
Display total qty sold of each item of each store?