-- 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
-- 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
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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE