troubleshooting Question

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

Avatar of Paula DiTallo
Paula DiTalloFlag for United States of America asked on
Microsoft SQL ServerSQL
4 Comments1 Solution84 ViewsLast Modified:
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

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
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros