SQL: How to combine values in column week (i.e. week 1, week 2, etc) into week range (week 1-2 or we-CodePudding

General SQL question using online SQLite editor, if I have the following table:

item	week	sales
item1	1	35
item2	1	25
item3	1	24
item1	2	35
item2	2	34
item1	3	24
item2	3	45
item1	4	44
item2	4	51
item1	5	1
item3	5	100

how would I get the output to be this:

item	weeks	total_sales
item1	1-2	70
item2	3-4	96
item3	5	100

More specifically, how would I (if possible) show the week as a range ('week 1-2' or even 'week 1, 2') rather than just 'week 1', where the range would be the week(s) that the item had the most sales for each week?

Some code I have been working on is as follows:

SELECT item, 
    SUM(sales) OVER (
          PARTITION BY week order by item) AS total_sales
FROM TABLE1
GROUP BY item;

The above code gets me the sum or total sales for the week, but I am completely lost as to how one would get the week as a range.

Any help or clarification is appreciated. Example code

CodePudding user response：

You can use a CASE expression to create the ranges and group by that expression and item:

SELECT item,
       CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END weeks,
       SUM(sales) total_sales
FROM table1
GROUP BY item, weeks;

See the demo.