July 24, 2018 at 1:59 pm
Hello everyone, I have not worked with rolling up quantities before and I am looking for some help.
I need (or would like) any time an item appears more than once in my result, the ItemNum to be only listed once, with the quality rolled up and the price * total (rolled up) quantity.
OUTPUT:
1115 Wine 8 298.93 0.00 298.92 (as the example for ItemNum = 1115)
Thank you for any assistance.
CREATE TABLE mytable(
1115 INTEGER NOT NULL PRIMARY KEY (ItemNum)
,WINE VARCHAR(23) NOT NULL (ItemName)
,1 INTEGER NOT NULL (QuanitySold)
,3599 VARCHAR(6) NOT NULL (ListPrice)
,000 VARCHAR(5) NOT NULL (Discount)
,3599 VARCHAR(6) NOT NULL (PriceSold)
);
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$55.99','$0.00','$55.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$19.99','$0.00','$19.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$59.99','$0.00','$59.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$59.99','$0.00','$59.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$39.99','$0.00','$39.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$17.99','$0.00','$17.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (1115,'WINE',1,'$29.99','$0.00','$29.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8500001756,'GASCON',1,'$22.99','$0.00','$22.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (87073100008,'NAPA CELLARS SAV BL',1,'$11.99','$0.00','$11.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (65789171559,'AUSTIN HOPE PASO',1,'$44.99','$0.00','$44.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (89807900100,'EDUCATED',1,'$22.99','$0.00','$22.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8500001622,'GHOST P',1,'$17.99','$0.00','$17.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8912118852,'J.LOHR MERLOT',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (83094900016,'ROBERT HALL PASO',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (66626292651,'TALBOT KALI HART',2,'$18.99','$0.00','$18.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (66626292651,'TALBOT KALI HART',1,'$18.99','$0.00','$18.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (83094900013,'ROBERT HALL PASO ROBLES',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (85370600203,'BUTTER CHARDONNY',2,'$17.99','$0.00','$17.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (85370600203,'BUTTER CHARDONNY',2,'$17.99','$0.00','$17.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (8600387388,'WOODBRIDGE CHARD',1,'$12.99','$0.00','$12.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (9792197010,'ROMBAUER CHARD',1,'$37.99','$0.00','$37.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (9792197010,'ROMBAUER CHARD',3,'$37.99','$0.00','$37.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (71589700913,'MORGAN SAUV BLANC',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (71589700913,'MORGAN SAUV BLANC',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable(1115,WINE,1,3599,000,3599) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
July 24, 2018 at 2:12 pm
your example was not syntactically correct, i fixed it for you.
the key you want here is to group by and SUM.
some things just don't belong in the results, like do you want to sum the discount amount? not without the quantity.
you need the sold price times the quantity, on a per row basis to satisfy your group by
youd datatypes for prices have to be decimal/money, otherwise you cannot do math on them
IF OBJECT_ID('[dbo].[mytable]') IS NOT NULL
DROP TABLE [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable] (
[ItemNum] bigint NOT NULL,
[ItemName] VARCHAR(23) NOT NULL,
[QuantitySold] INT NOT NULL,
[ListPrice] MONEY NOT NULL,
[Discount] MONEY NOT NULL,
[PriceSold] MONEY NOT NULL,
)
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$55.99','$0.00','$55.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$19.99','$0.00','$19.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$59.99','$0.00','$59.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$59.99','$0.00','$59.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$39.99','$0.00','$39.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$17.99','$0.00','$17.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (1115,'WINE',1,'$29.99','$0.00','$29.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8500001756,'GASCON',1,'$22.99','$0.00','$22.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8600300258,'WOODBRIDGE CHARD 4PK',1,'$6.99','$0.00','$6.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (87073100008,'NAPA CELLARS SAV BL',1,'$11.99','$0.00','$11.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (65789171559,'AUSTIN HOPE PASO',1,'$44.99','$0.00','$44.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (89807900100,'EDUCATED',1,'$22.99','$0.00','$22.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8500001622,'GHOST P',1,'$17.99','$0.00','$17.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8912118852,'J.LOHR MERLOT',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (83094900016,'ROBERT HALL PASO',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (66626292651,'TALBOT KALI HART',2,'$18.99','$0.00','$18.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (66626292651,'TALBOT KALI HART',1,'$18.99','$0.00','$18.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (83094900013,'ROBERT HALL PASO ROBLES',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (85370600203,'BUTTER CHARDONNY',2,'$17.99','$0.00','$17.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (85370600203,'BUTTER CHARDONNY',2,'$17.99','$0.00','$17.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (8600387388,'WOODBRIDGE CHARD',1,'$12.99','$0.00','$12.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (9792197010,'ROMBAUER CHARD',1,'$37.99','$0.00','$37.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (9792197010,'ROMBAUER CHARD',3,'$37.99','$0.00','$37.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (71589700913,'MORGAN SAUV BLANC',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (71589700913,'MORGAN SAUV BLANC',1,'$15.99','$0.00','$15.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
INSERT INTO mytable([ItemNum],[ItemName],[QuantitySold],[ListPrice],[Discount],[PriceSold]) VALUES (83094900011,'ROBERT HALL PASO ROBLES',1,'$14.99','$0.00','$14.99');
SELECT
MyTarget.[ItemNum],
MyTarget.[ItemName],
SUM(MyTarget.[QuantitySold]) AS QualtitySold ,
SUM(MyTarget.[QuantitySold] * MyTarget.[PriceSold]) AS TotalAmount
--MyTarget.[ListPrice],
--MyTarget.[Discount],
FROM MyTable AS MyTarget
GROUP BY MyTarget.[ItemNum],
MyTarget.[ItemName]
Lowell
July 24, 2018 at 2:24 pm
First, your table script is messed up. I suggest you update it.
Second, ROLLUP has a very specific meaning, which is not what you want here. Try searching on "SUM T-SQL".
Third, why are you treating single member sets differently from multiple member sets? A set is a set is a set. It doesn't matter how many members a set contains, you want to process them all the same way. You don't use a completely different method to scramble one egg than you do for multiple eggs, so why would you treat one row differently from multiple rows?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2018 at 2:27 pm
SELECT
[iv].[ItemNum]
,[ii].[DiffItemName]
,CONVERT(VARCHAR(8), SUM(CAST([ii].[Quantity] AS INT))) AS [#Sold]
,'$' + CONVERT(VARCHAR(9), SUM(CAST([ii].[Quantity] * [ii].[PricePer] AS MONEY))) AS [Total_Sales]
--MyTarget.[ListPrice],
--MyTarget.[Discount],
FROM
Invoice_Itemized as [ii]
JOIN Invoice_Totals as [it] ON [it].[Invoice_Number] = [ii].[Invoice_Number]
JOIN Inventory as [iv] ON [iv].[ItemNum] = [ii].[ItemNum]
WHERE Dept_ID IN (
'1115',
'1115A',
'1115B',
'1115C',
'1115D',
'1115E',
'1115F',
'1115G',
'1115H',
'1115I',
'1115J',
'1115K',
'1115L',
'1115M',
'1115N',
'1115O')
AND [it].[DateTime] >= @MonthStart AND [it].[DateTime] < @PeriodEnd
GROUP BY [iv].[ItemNum],
[ii].[DiffItemName]
ORDER BY [iv].[ItemNum] ASC
My completed code, I was so close!
Thank you sir for the response!
July 24, 2018 at 2:41 pm
drew.allen - Tuesday, July 24, 2018 2:24 PMFirst, your table script is messed up. I suggest you update it.
Second, ROLLUP has a very specific meaning, which is not what you want here. Try searching on "SUM T-SQL".
Third, why are you treating single member sets differently from multiple member sets? A set is a set is a set. It doesn't matter how many members a set contains, you want to process them all the same way. You don't use a completely different method to scramble one egg than you do for multiple eggs, so why would you treat one row differently from multiple rows?
Drew
Thank you for your direction, Drew.
July 24, 2018 at 2:48 pm
chef423 - Tuesday, July 24, 2018 2:27 PMSELECT
[iv].[ItemNum]
,[ii].[DiffItemName]
,CONVERT(VARCHAR(8), SUM(CAST([ii].[Quantity] AS INT))) AS [#Sold]
,'$' + CONVERT(VARCHAR(9), SUM(CAST([ii].[Quantity] * [ii].[PricePer] AS MONEY))) AS [Total_Sales]
--MyTarget.[ListPrice],
--MyTarget.[Discount],FROM
Invoice_Itemized as [ii]
JOIN Invoice_Totals as [it] ON [it].[Invoice_Number] = [ii].[Invoice_Number]
JOIN Inventory as [iv] ON [iv].[ItemNum] = [ii].[ItemNum]
WHERE Dept_ID IN (
'1115',
'1115A',
'1115B',
'1115C',
'1115D',
'1115E',
'1115F',
'1115G',
'1115H',
'1115I',
'1115J',
'1115K',
'1115L',
'1115M',
'1115N',
'1115O')
AND [it].[DateTime] >= @MonthStart AND [it].[DateTime] < @PeriodEndGROUP BY [iv].[ItemNum],
[ii].[DiffItemName]ORDER BY [iv].[ItemNum] ASC
My completed code, I was so close!
Thank you sir for the response!
You really don't want to format your data in T-SQL. Leave that to your reporting software. In other words, don't convert your INT and MONEY columns to VARCHAR just so you can add a dollar sign.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2018 at 9:08 am
drew.allen - Tuesday, July 24, 2018 2:48 PMchef423 - Tuesday, July 24, 2018 2:27 PMSELECT
[iv].[ItemNum]
,[ii].[DiffItemName]
,CONVERT(VARCHAR(8), SUM(CAST([ii].[Quantity] AS INT))) AS [#Sold]
,'$' + CONVERT(VARCHAR(9), SUM(CAST([ii].[Quantity] * [ii].[PricePer] AS MONEY))) AS [Total_Sales]
--MyTarget.[ListPrice],
--MyTarget.[Discount],FROM
Invoice_Itemized as [ii]
JOIN Invoice_Totals as [it] ON [it].[Invoice_Number] = [ii].[Invoice_Number]
JOIN Inventory as [iv] ON [iv].[ItemNum] = [ii].[ItemNum]
WHERE Dept_ID IN (
'1115',
'1115A',
'1115B',
'1115C',
'1115D',
'1115E',
'1115F',
'1115G',
'1115H',
'1115I',
'1115J',
'1115K',
'1115L',
'1115M',
'1115N',
'1115O')
AND [it].[DateTime] >= @MonthStart AND [it].[DateTime] < @PeriodEndGROUP BY [iv].[ItemNum],
[ii].[DiffItemName]ORDER BY [iv].[ItemNum] ASC
My completed code, I was so close!
Thank you sir for the response!
You really don't want to format your data in T-SQL. Leave that to your reporting software. In other words, don't convert your INT and MONEY columns to VARCHAR just so you can add a dollar sign.
Drew
Drew, I have a cirtial question. I really need to add a column called 'ListPrice' which is from the same Invoice_Itemized table but its not 'rolled up' or grouped, I just need the that static price to sit in a column in that query, the the Price listing in the Point of Sale for that product. Is that not possible? When I put it in, now my items are no longer grouped, fully.
Please advise. Surely there is some way to get that in there cleanly?
July 25, 2018 at 9:31 am
Eh, I figured it out...now to get the '#Sold' in ASC order by INT..its currently treating 1 then 11 then 12 then 2....so its reading an 11 as the successor to 1
EDIT: ok CAST(List_Price As INT) ASC will work, but now I get the error 'List_Price' is not in the GROUP BY, but when I add it to the group by, my grouping gets distorted.
July 25, 2018 at 10:40 am
chef423 - Wednesday, July 25, 2018 9:31 AMEh, I figured it out...now to get the '#Sold' in ASC order by INT..its currently treating 1 then 11 then 12 then 2....so its reading an 11 as the successor to 1
And this is exactly why you want to leave your formatting to the reporting software. Your column is a character column, so it's sorting alphabetically not numerically.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply