CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 1674

MySQL: How do I create a query that returns rows from last 30 days and 15 days?

Currently I'm trying to create a query that retrieves values from my issues table from the last 15 days and also the last 30 days. I'm able to return values for these in separate queries but I want to know if it's possible to do this within one. As of right now the query that I'm using looks like this:

    SELECT 
        issue, COUNT(id) AS total_issues
    FROM
        mytable
    WHERE
        issue_date BETWEEN DATE(NOW() - INTERVAL 30 DAY) AND DATE(NOW())
            AND issue != 'Currently Unavailable'
            AND issue != 'Out Of Stock'
    GROUP BY issue;

This will return the issues in the past 30 days along with the total amount of times they occurred within this period like so:

|issue       |total_issues|
|issue1      |12          |
|issue2      |24          |
|issue3      |2           |

But what I would like to get is something more along the lines of this:

|issues_30   |total_issues|
|issue1      |12          |
|issue2      |24          |
|issue3      |2           |

|issues_15   |total_issues|
|issue1      |8           |
|issue2      |14          |

I have looked online to see if there was a way to return values between specific intervals like this but only found answers to questions just regarding one interval of time instead of multiple.

I don't know if this is currently doable in MySQL or if I just have no other choice then to make these all separate queries. Say not only did I want values from the last 30 and 15 days but also the last 7 as well. This would mean that I would have to add another query to just get the issues from the last 7 days as well which seems like bad practice. The only thing that makes me think that this might not be doable is that for each different interval of time not all of the issues will show up (just like in my example where issue3 is dropped off because it only occurs in the last 30 days. So I would probably expect results like this instead:

|issues_30   |total_issues|
|issue1      |12          |
|issue2      |24          |
|issue3      |2           |

|issues_15   |total_issues|
|issue1      |8           |
|issue2      |14          |
|issue3      |0           |

If anyone has any experience in creating queries like this and could help me understand what needs to be done to accomplish this that would be very helpful. TIA!

Upvotes: 1

Views: 36

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

I prefer this version, which uses a single query with conditional aggregation to find the 2 types of counts:

SELECT 
    issue,
    COUNT(CASE WHEN issue_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
               THEN 1 END) AS issues_last_30,
    COUNT(CASE WHEN issue_date BETWEEN CURDATE() - INTERVAL 15 DAY AND CURDATE()
               THEN 1 END) AS issues_last_15,
    COUNT(*) AS total_issues
FROM mytable
WHERE
    issue != 'Currently Unavailable' AND
    issue != 'Out Of Stock'
GROUP BY
    issue;

Upvotes: 2

Related Questions