Has to be a better way using over(partition by) in SQL

mattkovo
mattkovo used Ask the Experts™
on
Instead of using nested Select Statements I would think I could use the over(partition by) statement here.  I was able to produce something close but it made me group by 'Item' AND 'Qty' so my output was not desireable.

Any help here would be GREATLY appreciated!
(
SELECT
	MASTER_MENU_ITEMS.MENU_ITEM_NAME as 'Item', 
    SUM(ITEM_SUMMARY.Qty) as 'Qty', 
    convert(decimal(9,2), sum(ITEM_SUMMARY.Qty)) / convert(decimal(9,2), 
    (SELECT sum(ITEM_SUMMARY.Qty) as 'Qty' FROM MASTER_FILTER_ITEMS INNER JOIN (ITEM_SUMMARY INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID) ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID WHERE ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate AND MASTER_FILTER_ITEMS.FILTERID = 25 AND Item_Summary.Extension + Item_Summary.Discount_Amount > 0 ))*100 as 'Qty Percentage', 
    (SELECT sum(ITEM_SUMMARY.Qty) as 'Qty' FROM MASTER_FILTER_ITEMS INNER JOIN (ITEM_SUMMARY INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID) ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID WHERE ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate AND MASTER_FILTER_ITEMS.FILTERID = 25 AND Item_Summary.Extension + Item_Summary.Discount_Amount > 0 ) AS 'Qty Total',
    SUM(Item_Summary.Extension + Item_Summary.Discount_Amount) as 'Sales',
	sum(Item_Summary.Extension + Item_Summary.Discount_Amount)/(SELECT sum(Item_Summary.Extension + Item_Summary.Discount_Amount) as 'Sales' FROM MASTER_FILTER_ITEMS INNER JOIN (ITEM_SUMMARY INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID) ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID WHERE ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate AND MASTER_FILTER_ITEMS.FILTERID = 25 and Item_Summary.Extension + Item_Summary.Discount_Amount > 0 )*100 as 'Total Percentage' ,
	(SELECT sum(Item_Summary.Extension + Item_Summary.Discount_Amount) as 'Sales' FROM MASTER_FILTER_ITEMS INNER JOIN (ITEM_SUMMARY INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID) ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID WHERE ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate AND MASTER_FILTER_ITEMS.FILTERID = 25 and Item_Summary.Extension + Item_Summary.Discount_Amount > 0 ) AS 'Sales Total'
 
FROM
      MASTER_FILTER_ITEMS 
      INNER JOIN (ITEM_SUMMARY INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID) ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID
      
WHERE  
      ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate
      AND MASTER_FILTER_ITEMS.FILTERID = 25
      and Item_Summary.Extension + Item_Summary.Discount_Amount > 0
      
Group by MASTER_MENU_ITEMS.MENU_ITEM_NAME
)
ORDER BY 'Qty' Desc

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
you can use temp tables to get the result set you wanted.. examine this..
CREATE TABLE #ITEM_TOTAL (
	QUANTITY	int,
	SALES		int	
);
INSERT INTO #ITEM_TOTAL
SELECT sum(ITEM_SUMMARY.Qty), sum(Item_Summary.Extension + Item_Summary.Discount_Amount) 
FROM MASTER_MENU_ITEMS 
	INNER JOIN ITEM_SUMMARY ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID
	INNER JOIN MASTER_FILTER_ITEMS ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID
WHERE ITEM_SUMMARY.CHECK_FILE_DATE BETWEEN @StartDate AND @EndDate 
  AND MASTER_FILTER_ITEMS.FILTERID = 25 
  AND Item_Summary.Extension + Item_Summary.Discount_Amount > 0
 
 
 
CREATE TABLE #ITEM_SUMMARY (
	ITEM_NAME	varchar(50),
	QUANTITY	int,
	SALES		int	
);
INSERT INTO #ITEM_SUMMARY
SELECT MASTER_MENU_ITEMS.MENU_ITEM_NAME, sum(ITEM_SUMMARY.Qty), sum(Item_Summary.Extension + Item_Summary.Discount_Amount) 
FROM MASTER_MENU_ITEMS 
	INNER JOIN ITEM_SUMMARY ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID
	INNER JOIN MASTER_FILTER_ITEMS ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID
WHERE ITEM_SUMMARY.CHECK_FILE_DATE BETWEEN @StartDate AND @EndDate 
  AND MASTER_FILTER_ITEMS.FILTERID = 25 
  AND Item_Summary.Extension + Item_Summary.Discount_Amount > 0
GROUP BY MASTER_MENU_ITEMS.MENU_ITEM_NAME;
 
 
 
SELECT 
	S.ITEM_NAME as 'Item',
	S.QUANTITY as 'Qty', 
	(S.QUANTITY * 1.0 / T.QUANTITY)*100 as 'Qty Percentage', 
	T.QUANTITY as 'Qty Total',
	S.SALES as 'Sales',
	(S.SALES *1.0 / T.SALES) *100 as 'Total Percentage' ,
	T.SALES AS 'Sales Total' 
FROM #ITEM_SUMMARY S, #ITEM_TOTAL T

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this simplifies
SELECT Item, 
       Qty,
       'Total Qty',
       (cast(Qty AS decimal (9,2)) * 100) / cast([Total Qty] AS decimal (9,2)) AS 'Qty Percentage',
        Sales,
       'Sales Total',
       (cast(Qty AS decimal (9,2)) * 100) / cast([Sales Total] AS decimal(9,2)) AS 'Qty Percentage'
FROM (
SELECT MASTER_MENU_ITEMS.MENU_ITEM_NAME as 'Item', 
       SUM(ITEM_SUMMARY.Qty) OVER( partition BY MASTER_MENU_ITEMS.MENU_ITEM_NAME) as 'Qty', 
       SUM(ITEM_SUMMARY.Qty) as 'Total Qty',
       SUM(Item_Summary.Extension + Item_Summary.Discount_Amount)  OVER( partition BY MASTER_MENU_ITEMS.MENU_ITEM_NAME) as 'Sales',
       SUM(Item_Summary.Extension + Item_Summary.Discount_Amount) AS 'Sales Total'
FROM ITEM_SUMMARY 
     INNER JOIN MASTER_MENU_ITEMS ON ITEM_SUMMARY.ID = MASTER_MENU_ITEMS.MENUID
     INNER JOIN MASTER_FILTER_ITEMS ON MASTER_FILTER_ITEMS.MENU_ITEM = MASTER_MENU_ITEMS.MENUID
WHERE ITEM_SUMMARY.CHECK_FILE_DATE between @StartDate and @EndDate
  AND MASTER_FILTER_ITEMS.FILTERID = 25
  AND Item_Summary.Extension + Item_Summary.Discount_Amount > 0
GROUP BY MASTER_MENU_ITEMS.MENU_ITEM_NAME) temp

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Missed ORDER BY statement. Kindly include this at the end of my query

ORDER BY Qty DESC

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