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

Time:04-28

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.

  • Related