add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64)
Jul 25 2020 11:26:55
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
CREATE TABLE Table5 (
product varchar(20)
, weeknumber int
, sales int
);
INSERT INTO Table5 VALUES
('a', 3, 63)
, ('a', 4, 62)
, ('a', 5, 59)
, ('a', 1, 58)
, ('b', 4, 44)
, ('b', 5, 52)
, ('b', 1, 59)
, ('c', 5, 77)
, ('c', 1, 21)
;
9 rows affected
-- Original SQL with the error:

select *, max(sales)
from
(
select product, '1-4' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (1,2,3,4)
group by product
union
select product, '2-5' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (2,3,4,5)
group by product
union
select product, '3-6' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (3,4,5,6)
group by product
union
select product, '4-7' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (4,5,6,7)
group by product
union
select product, '5-8' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (5,6,7,8)
group by product
)a
group by product
order by product, sales desc
Msg 8120 Level 16 State 1 Line 3
Column 'a.weeks' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select TOP 1 *
from (
select product, '1-4' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (1,2,3,4)
group by product
union
select product, '2-5' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (2,3,4,5)
group by product
union
select product, '3-6' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (3,4,5,6)
group by product
union
select product, '4-7' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (4,5,6,7)
group by product
union
select product, '5-8' as weeks, sum(sales)as sales from Table5 t
where t.WEEKNUMBER IN (5,6,7,8)
group by product
) a
order by sales desc
product weeks sales
a 2-5 184
WITH xrows AS (
select a.*
, row_number() OVER (ORDER BY sales DESC) AS n
from (
select product, '1-4' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (1,2,3,4)
group by product
union
select product, '2-5' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (2,3,4,5)
group by product
union
select product, '3-6' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (3,4,5,6)
group by product
union
select product, '4-7' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (4,5,6,7)
group by product
union
select product, '5-8' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (5,6,7,8)
group by product
) a
)
SELECT *
FROM xrows
WHERE n = 1
;

product weeks sales n
a 2-5 184 1
-- Find the highest sales product per week:

WITH xrows AS (
select a.*
, row_number() OVER (PARTITION BY weeks ORDER BY sales DESC) AS n
from (
select product, '1-4' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (1,2,3,4)
group by product
union
select product, '2-5' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (2,3,4,5)
group by product
union
select product, '3-6' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (3,4,5,6)
group by product
union
select product, '4-7' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (4,5,6,7)
group by product
union
select product, '5-8' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (5,6,7,8)
group by product
) a
)
SELECT *
FROM xrows
WHERE n = 1
ORDER BY weeks
;

product weeks sales n
a 1-4 183 1
a 2-5 184 1
a 3-6 184 1
a 4-7 121 1
c 5-8 77 1
-- Find the highest sales week per product:

WITH xrows AS (
select a.*
, row_number() OVER (PARTITION BY product ORDER BY sales DESC) AS n
from (
select product, '1-4' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (1,2,3,4)
group by product
union
select product, '2-5' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (2,3,4,5)
group by product
union
select product, '3-6' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (3,4,5,6)
group by product
union
select product, '4-7' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (4,5,6,7)
group by product
union
select product, '5-8' as weeks, sum(sales) as sales from Table5 t
where t.WEEKNUMBER IN (5,6,7,8)
group by product
) a
)
SELECT *
FROM xrows
WHERE n = 1
ORDER BY product
;

product weeks sales n
a 2-5 184 1
b 1-4 103 1
c 2-5 77 1
CREATE TABLE Table6 (
product varchar(20)
, week int
, sales int
);
INSERT INTO Table6 VALUES
('a', 3, 63)
, ('a', 4, 62)
, ('a', 5, 59)
, ('a', 1, 58)
, ('b', 4, 44)
, ('b', 5, 52)
, ('b', 1, 59)
, ('c', 5, 77)
, ('c', 1, 21)
;
9 rows affected
select product, week - 3 as start_week, week as end_week, sales_4
from (select product, week, sum_sales, sales_4,
row_number() over (partition by product order by sales_4 desc) as seqnum
from (select product, week, sum(sales) as sum_sales,
sum(sum(sales)) over (partition by product
order by week
rows between 3 preceding and current row
) as sales_4
from table6 t
group by product, week
) t
where week >= 4 and week <= 8
) t
where seqnum = 1;
product start_week end_week sales_4
a 2 5 242
b 2 5 155
c 2 5 98
with s as (
select product, weeknumber, sum(sales) as sum_sales
from table5 t
group by product, weeknumber
)
select product, weeknumber - 3 as start_week, weeknumber as end_week, sales_4
from (select s.*, s2.sales_4,
row_number() over (partition by product order by sales_4 desc) as seqnum
from s outer apply
(select sum(s2.sum_sales) as sales_4
from s s2
where s2.product = s.product and
s2.weeknumber <= s.weeknumber and
s2.weeknumber >= s.weeknumber - 3
) s2
) s
where seqnum = 1 and s.weeknumber between 4 and 8;
product start_week end_week sales_4
a 2 5 184
b 1 4 103
c 2 5 77
WITH args (numweeks, numperiods, startweek) AS (
SELECT 4, 5, 1
)
, periods (week1, week2, nw) AS (
SELECT startweek, startweek+numweeks-1, numperiods FROM args UNION ALL
SELECT week1+1, week2+1, nw - 1 FROM periods
WHERE nw > 1
)
, calcweeks AS (
SELECT product, week1, week2, SUM(sales) AS sales
, ROW_NUMBER() OVER (PARTITION BY product ORDER BY SUM(sales) DESC) AS n
FROM table5 AS t
JOIN periods AS p
ON t.weeknumber BETWEEN p.week1 AND p.week2
GROUP BY product, week1, week2
)
SELECT *
FROM calcweeks
WHERE n = 1
ORDER BY product
;

/*
+---------+-------+-------+-------+---+
| product | week1 | week2 | sales | n |
+---------+-------+-------+-------+---+
| a | 3 | 6 | 184 | 1 |
| b | 1 | 4 | 103 | 1 |
| c | 3 | 6 | 77 | 1 |
+---------+-------+-------+-------+---+
*/
product week1 week2 sales n
a 2 5 184 1
b 1 4 103 1
c 2 5 77 1