Failed SQL SUM/group or SUM/partion over statement.

Paula DiTallo
Paula DiTallo used Ask the Experts™
on
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 #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

Open in new window


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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Ryan ChongSoftware Team Lead

Commented:
you should use:

sum(s.qty*s.price) as Amt

Open in new window


instead of

sum(s.price) as Amt

Open in new window


to implement:

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

Open in new window

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
For performance reasons, I'd delay looking up the category until after the products alone had been grouped.  This approach also makes it easier and more natural to allow for a missing category entry (which should never happen but you never know).  Or to allow at some point a product to be counted in two (or more) different categories ... again, you never know.

SELECT S.Store, S.SaleDate, C.categoryname, SUM(qty) AS qty, SUM(Amt) AS Amt
FROM (
    SELECT Store, SaleDate, productname, SUM(qty) AS qty, SUM(qty * price) AS Amt
    FROM #sales
    GROUP BY Store, SaleDate, productname
) AS S
LEFT OUTER JOIN #categories AS C ON C.productname = S.productname
GROUP BY S.Store, S.SaleDate, C.categoryname
ORDER BY S.Store, S.SaleDate, C.categoryname DESC
Paula DiTalloIntegration developer

Author

Commented:
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 DiTalloIntegration developer

Author

Commented:
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.

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