Group by with categories created within the query in SQL-CodePudding

I have a table where I want to group by both categories and days, however I want to organize the days into separate buckets and apparently I do not find a way to do it.

Table:

| Days       | Category    | Values        |
|          2 |           A |            20 |
|          4 |           B |            50 |
|          6 |           A |           100 |
|          2 |           A |            70 |
|          1 |           B |           220 |
|          9 |           A |           130 |
|          7 |           A |            45 |
|          1 |           A |            90 |
|          5 |           B |           280 |
|          5 |           B |            10 |
|          8 |           A |            70 |
|          9 |           B |            50 |
|          0 |           A |           120 |
|          3 |           B |           115 |
|          0 |           B |            25 |
|          3 |           B |            10 |
|          6 |           A |            55 |

The result I would like to get:

| Days       | Category    | Values        |
|        0-4 |           A |           300 |
|        0-4 |           B |           420 |
|        5-9 |           A |           400 |
|        5-9 |           B |           340 |

Based on my current knowledge this is how far I can get:

SELECT
 Days, Category, Value
FROM
 Table
GROUP BY
 Days,
 Category

But of course I cannot create the day buckets. Can you please help me out with it?

CodePudding user response：

This could get expected results

SELECT
  CONCAT_WS('-', MIN(FLOOR(`Days` / 5)) * 5, MIN(FLOOR(`Days` / 5)) * 5   4) Days,
  `Category`,
  SUM(`Values`) "Values"
FROM data
GROUP BY FLOOR(`Days` / 5), Category

With data

CREATE TABLE data (
 `Days` INT,
 `Category` VARCHAR(10),
 `Values` INT
);

INSERT INTO data VALUES 
('2','A','20'),('4','B','50'),('6','A','100'),('2','A','70'),('1','B','220'),('9','A','130'),('7','A','45'),('1','A','90'),('5','B','280'),('5','B','10'),('8','A','70'),('9','B','50'),('0','A','120'),('3','B','115'),('0','B','25'),('3','B','10'),('6','A','55');

CodePudding user response：

Use the DIV operator (DIV = Integer division. Discards from the division result any fractional part to the right of the decimal point):

  SELECT CONCAT(
           MIN(Days DIV 5) * 5,
           ' – ',
           (MIN(Days DIV 5)   1) * 5 - 1
         ) AS Days,
         Category,
         SUM(`Values`) AS `Values`
    FROM `Table`
GROUP BY Days DIV 5,
         Category

sqlfiddle