Failed SQL SUM/group or SUM/partion over statement.
I am attempting to group/sum/partition over by category after joining a sales table with a product table. I am not getting the expected results. Here are the expected results I'm looking to achieve:
-- Desired resultset
-- Store SaleDate Qty Amt Category
-- 1 2020-01-01 30 3.00 vegetable
-- 1 2020-01-01 35 11.75 fruit
-- 2 2020-01-01 8 10.56 vegetable
-- 2 2020-01-01 20 7.00 fruit
-- 1 2020-01-02 28 8.40 fruit
Here is the build script (t-sql mssql)
-- create tables-- drop table #categoriescreate table #categories(categoryname varchar(20), productname varchar(20))-- drop table #salescreate table #sales(Store int, SaleDate date, productname varchar(20), qty int, price decimal(4,2))-- create categoriesinsert into #categories(categoryname,productname)values('fruit','apple')insert into #categories(categoryname,productname)values('fruit','orange')insert into #categories(categoryname,productname)values('vegetable','carrot')insert into #categories(categoryname,productname)values('vegetable','cabbage')-- generate sales data for a storeinsert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-01' as date),'carrot',20,.10)insert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-01' as date),'carrot',10,.10) insert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-01' as date),'apple',10,.30) insert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-01' as date),'orange',10,.35)insert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-01' as date),'orange',15,.35)-- generate sales for another storeinsert into #sales(Store,SaleDate,productname,qty,price)values(2,cast('2020-01-01' as date),'cabbage',8,1.32)insert into #sales(Store,SaleDate,productname,qty,price)values(2,cast('2020-01-01' as date),'orange',20,.35)-- store #1, a different dayinsert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-02' as date),'apple',8,.30)insert into #sales(Store,SaleDate,productname,qty,price)values(1,cast('2020-01-02' as date),'apple',20,.30)-- Look at what got createdselect * from #categoriesselect * from #sales
I have tried these two queries--the results are not what I am expecting to see. What am I doing incorrectly?
-- group by attemptselect s.Store, s.SaleDate, c.CategoryName, sum(s.qty) as Qty, sum(s.price) as Amt from #sales s inner join #categories c on c.productname = s.productname group by s.Store, s.SaleDate, c.CategoryName order by Store,SaleDate, categoryname desc-- partition over attempt select s.Store, s.SaleDate, c.CategoryName, sum(s.qty) over(partition by c.categoryname) as Qty, sum(s.price) over(partition by c.categoryname) as Amt from #sales s inner join #categories c on c.productname = s.productname order by Store,SaleDate,CategoryName desc
select s.Store, s.SaleDate, c.CategoryName, sum(s.qty) as Qty, sum(s.qty*s.price) as Amt from #sales s inner join #categories c on c.productname = s.productname group by s.Store, s.SaleDate, c.CategoryName order by Store,SaleDate, categoryname desc
Scott,
Brilliantly done! Thanks so much for your help. I like that you are thinking ahead for a reality we all wish would never happen (missing categories), but know in our hearts it will! :)
Paula DiTallo
ASKER
Ryan,
Thank you for working on this, I appreciate it. Your statement did work. I chose Scott's because think I should take into account both the possibility of a missing category and his performance suggestion.
Open in new window
instead of
Open in new window
to implement:
Open in new window