Paula DiTallo
asked on
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)
I have tried these two queries--the results are not what I am expecting to see. What am I doing incorrectly?
-- 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 #categories
create table #categories
(categoryname varchar(20),
productname varchar(20)
)
-- drop table #sales
create table #sales
(Store int,
SaleDate date,
productname varchar(20),
qty int,
price decimal(4,2)
)
-- create categories
insert 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 store
insert 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 store
insert 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 day
insert 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 created
select * from #categories
select * 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 attempt
select 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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! :)
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! :)
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.
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