December 14, 2019 at 1:42 am
I have a dataset that I want to track the number of cars, trucks, and suvs sold by year, dealer, and week. I would like a cumulative total by dealer by week. The code I wrote works well for what I want except for not resetting the totals to 0 for each change of dealership.
Here is the code:
Drop table if exists Car_Sales
Create Table Car_Sales(Year varchar(4),Dealer varchar(10),Week varchar(2),Cars int,Trucks Int,SUVs Int)
insert into Car_Sales values
('2018','Ford','01',3,2,4),
('2018','Ford','02',4,9,9),
('2018','Ford','03',3,1,9),
('2018','Ford','04',6,1,5),
('2018','Ford','05',8,2,0),
('2018','Ford','06',2,1,3),
('2018','Ford','07',5,5,2),
('2018','Ford','08',7,4,3),
('2018','Ford','09',6,1,2),
('2018','Ford','10',0,6,8),
('2018','Dodge','01',5,4,0),
('2018','Dodge','02',6,8,0),
('2018','Dodge','03',4,8,8),
('2018','Dodge','04',2,8,5),
('2018','Dodge','05',1,8,6),
('2018','Dodge','06',4,2,6),
('2018','Dodge','07',9,9,5),
('2018','Dodge','08',3,5,2),
('2018','Dodge','09',8,2,8),
('2018','Dodge','10',1,9,5),
('2018','GMC','01',2,2,1),
('2018','GMC','02',7,7,2),
('2018','GMC','03',2,6,2),
('2018','GMC','04',1,6,0),
('2018','GMC','05',9,0,3),
('2018','GMC','06',2,9,6),
('2018','GMC','07',9,5,0),
('2018','GMC','08',9,4,0),
('2018','GMC','09',3,8,5),
('2018','GMC','10',2,0,5),
('2018','Mercedes','01',0,3,2),
('2018','Mercedes','02',8,2,3),
('2018','Mercedes','03',8,5,1),
('2018','Mercedes','04',9,0,1),
('2018','Mercedes','05',9,4,8),
('2018','Mercedes','06',3,4,8),
('2018','Mercedes','07',4,6,1),
('2018','Mercedes','08',1,4,3),
('2018','Mercedes','09',7,9,3),
('2018','Mercedes','10',8,7,6);
select
Year
,Dealer
,Week
,Cars
,Trucks
,SUVs
,Concat(
'('
,Sum(Cars) over (order by Year,Dealer,Week)
,'-'
,Sum(Trucks) over (order by Year,Dealer,Week)
,'-'
,Sum(SUVS) over (order by Year,Dealer,Week)
,')'
) as Record
from Car_Sales
As you can see in the data below, the cumulative totals keep adding up even though the dealer goes from Dodge to Ford and from Ford to GMC and from GMC to Mercedes. I would like to start the count over again for each dealer. I tried partition by and group by and haven't had much luck. Any suggestions? Thanks
Year Dealer Week Cars Trucks SUVs Record
2018 Dodge 01 5 4 0 (5-4-0)
2018 Dodge 02 6 8 0 (11-12-0)
2018 Dodge 03 4 8 8 (15-20-8)
2018 Dodge 04 2 8 5 (17-28-13)
2018 Dodge 05 1 8 6 (18-36-19)
2018 Dodge 06 4 2 6 (22-38-25)
2018 Dodge 07 9 9 5 (31-47-30)
2018 Dodge 08 3 5 2 (34-52-32)
2018 Dodge 09 8 2 8 (42-54-40)
2018 Dodge 10 1 9 5 (43-63-45)
2018 Ford 01 3 2 4 (46-65-49)
2018 Ford 02 4 9 9 (50-74-58)
2018 Ford 03 3 1 9 (53-75-67)
2018 Ford 04 6 1 5 (59-76-72)
2018 Ford 05 8 2 0 (67-78-72)
2018 Ford 06 2 1 3 (69-79-75)
2018 Ford 07 5 5 2 (74-84-77)
2018 Ford 08 7 4 3 (81-88-80)
2018 Ford 09 6 1 2 (87-89-82)
2018 Ford 10 0 6 8 (87-95-90)
2018 GMC 01 2 2 1 (89-97-91)
2018 GMC 02 7 7 2 (96-104-93)
2018 GMC 03 2 6 2 (98-110-95)
2018 GMC 04 1 6 0 (99-116-95)
2018 GMC 05 9 0 3 (108-116-98)
2018 GMC 06 2 9 6 (110-125-104)
2018 GMC 07 9 5 0 (119-130-104)
2018 GMC 08 9 4 0 (128-134-104)
2018 GMC 09 3 8 5 (131-142-109)
2018 GMC 10 2 0 5 (133-142-114)
2018 Mercedes 01 0 3 2 (133-145-116)
2018 Mercedes 02 8 2 3 (141-147-119)
2018 Mercedes 03 8 5 1 (149-152-120)
2018 Mercedes 04 9 0 1 (158-152-121)
2018 Mercedes 05 9 4 8 (167-156-129)
2018 Mercedes 06 3 4 8 (170-160-137)
2018 Mercedes 07 4 6 1 (174-166-138)
2018 Mercedes 08 1 4 3 (175-170-141)
2018 Mercedes 09 7 9 3 (182-179-144)
2018 Mercedes 10 8 7 6 (190-186-150)
December 14, 2019 at 1:53 am
" I would like to start the count over again for each dealer"
You need to use PARTITION BY to do that. It's analogous to GROUP BY, but works inside windowing functions:
SELECT Dealer, SaleYear, SaleWeek, Cars,
SUM(Cars) OVER (PARTITION BY Dealer
ORDER BY SaleYear, SaleWeek
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS rtCars
FROM Car_Sales
ORDER BY Dealer, SaleYear, SaleWeek;
December 14, 2019 at 2:22 am
Thank you Pietlinden!!! That is exactly what I was looking for. I tried to use partition but didn't have the rest of it. It is going in my code file.
Below is the code that I incorporated your corrections.
Thanks again.
Drop table if exists Car_Sales
Create Table Car_Sales(Year varchar(4),Dealer varchar(10),Week varchar(2),Cars int,Trucks Int,SUVs Int)
insert into Car_Sales values('2018','Ford','01',3,2,4),('2018','Ford','02',4,9,9),('2018','Ford','03',3,1,9),('2018','Ford','04',6,1,5),('2018','Ford','05',8,2,0),('2018','Ford','06',2,1,3),('2018','Ford','07',5,5,2),('2018','Ford','08',7,4,3),('2018','Ford','09',6,1,2),('2018','Ford','10',0,6,8),('2018','Dodge','01',5,4,0),('2018','Dodge','02',6,8,0),('2018','Dodge','03',4,8,8),('2018','Dodge','04',2,8,5),('2018','Dodge','05',1,8,6),('2018','Dodge','06',4,2,6),('2018','Dodge','07',9,9,5),('2018','Dodge','08',3,5,2),('2018','Dodge','09',8,2,8),('2018','Dodge','10',1,9,5),('2018','GMC','01',2,2,1),
('2018','GMC','02',7,7,2),
('2018','GMC','03',2,6,2),
('2018','GMC','04',1,6,0),
('2018','GMC','05',9,0,3),
('2018','GMC','06',2,9,6),
('2018','GMC','07',9,5,0),
('2018','GMC','08',9,4,0),
('2018','GMC','09',3,8,5),
('2018','GMC','10',2,0,5),
('2018','Mercedes','01',0,3,2),
('2018','Mercedes','02',8,2,3),
('2018','Mercedes','03',8,5,1),
('2018','Mercedes','04',9,0,1),
('2018','Mercedes','05',9,4,8),
('2018','Mercedes','06',3,4,8),
('2018','Mercedes','07',4,6,1),
('2018','Mercedes','08',1,4,3),
('2018','Mercedes','09',7,9,3),
('2018','Mercedes','10',8,7,6);
select Year
,Dealer
,Week
,Cars
,Trucks
,SUVs
,Concat('(',SUM(Cars) OVER (PARTITION BY Dealer ORDER BY Year, Week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,'-'
,SUM(Trucks) OVER (PARTITION BY Dealer ORDER BY Year, Week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,'-'
,SUM(Suvs) OVER (PARTITION BY Dealer ORDER BY Year, Week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,')'
) as Record
from Car_Sales
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply