Is there a way in MySQL to select specific columns based on dates entered as variables?

For example, if there is a table containing daily data from Day 1 to Day 31 for various items. Like the table below -

This goes up to Day 31 if needed for any number of items in the month.

I'd like to be able to enter a date range such as 2021-12-01 and 2021-12-03 along with the item name 'Bag' and get the sum of the values in that data range - 2 4 2 = 8.

Is this possible?

And if it isn't possible with this current method I'm attempting, is there a way to get it done?

Thanks.

CodePudding user response：

This may not be the answer you want, but you should seriously fix your data model. Do not store your dates as columns, but rather store each date's data point in a separate record. So the first record from your sample table would become:

id | Item | Date       | num_items
1  | Bag  | 2021-12-01 | 2
1  | Bag  | 2021-12-02 | 4
1  | Bag  | 2021-12-03 | 2
1  | Bag  | 2021-12-04 | 5
1  | Bag  | 2021-12-05 | 1

Now your query becomes much more tractable:

SELECT id, Item, SUM(num_items) AS total
FROM yourTable
WHERE Date BETWEEN '2021-12-01' AND '2021-12-03' AND Item = 'Bag'
GROUP BY id, Item;

CodePudding user response：

a bit verbose, but you can do something like this :

SELECT
   IF (DAY('2021-12-01') <= 1 AND DAY('2021-12-03') >= 1, day1, 0)
   IF (DAY('2021-12-01') <= 2 AND DAY('2021-12-03') >= 2, day2, 0)
   IF (DAY('2021-12-01') <= 3 AND DAY('2021-12-03') >= 3, day3, 0)
   ...

Although it's probably better to change your data model to have a date and value as column.