SQL Server 2019

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