We help IT Professionals succeed at work.
Get Started

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

81 Views
Last Modified: 2020-01-15
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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE