I have data that looks like this:
name |sides|start |end
----------+-----+----------+----------
STR00248 |2 |2021-01-04|
STR00250 |1 |2021-01-04|
STR00246 |1 |2021-03-29|2021-09-12
STR00247 |1 |2021-03-29|2021-04-25
STR00248 |2 |2021-03-29|2021-09-12
STR00247 |1 |2021-03-29|2021-09-12
STR00248 |2 |2021-05-11|2021-08-10
STR00250 |1 |2021-05-24|2021-11-07
STR00250 |1 |2021-07-01|
STR00250 |1 |2021-07-19|2022-06-19
I want to get the sum of sides
for STR00250
grouped by unique combinations of start
and end
. If end
is NULL, it should be considered not to end. So the result should look something like this:
sum
---
1 (2021-01-04 - 2021-05-23)
2 (2021-05-24 - 2021-06-30)
3 (2021-07-01 - 2021-07-18)
4 (2021-07-19 - 2022-06-18)
3 (2022-06-19 - 2021-11-07)
2 (2021-11-08 - forward)
I don't need the dates, just the sums, although it would be nice to have the dates for error message purposes (in which case they should be in a separate column). Unfortunately browsing StackExchange hasn't triggered any ideas on how to accomplish my goal.
Here is the table structure and some sample data:
CREATE TABLE sample (
`name` VARCHAR(140),
`sides` INT(11),
`start` DATE,
`end` DATE
);
INSERT INTO sample VALUES
('STR00248', 2, '2021-01-04', NULL),
('STR00250', 1, '2021-01-04', NULL),
('STR00246', 1, '2021-03-29', '2021-09-12'),
('STR00247', 1, '2021-03-29', '2021-04-25'),
('STR00248', 2, '2021-03-29', '2021-09-12'),
('STR00247', 1, '2021-03-29', '2021-09-12'),
('STR00248', 2, '2021-05-11', '2021-08-10'),
('STR00250', 1, '2021-05-24', '2021-11-07'),
('STR00250', 1, '2021-07-01', NULL),
('STR00250', 1, '2021-07-19', '2022-06-19');
end
dates that areNULL
, is that equivalent to9999-12-31
? (i.e. the syntactic end of time) or is it inferred to be the day before the next start date or something?STR00250
only has 4 rows which sum up to 4, and most of your end dates don't exist in the table.2022-06-19 - 2021-11-07
doesn't make sense as start and end dates, and also aren't in the table. Please explain the logic you are trying to achieve2022-06-19 - 2021-11-07
make sense, and how does2022-06-19
become a start and not an end? You also say "grouped by unique combinations of start and end" what does unique combinations mean here? Please detail the logic, it's really unclear