CODEWITHSUNDEEP
mysqlmariadb
Tominator
Tominator

Reputation: 1224

Generalising my query to run on all data instead of specific id

I have a specific query, that works when I hardcode the ID it should run for. I would like to run it for all IDs in my database.

This is a representative setup:

CREATE TABLE `dummy` (
    `total` INT(11) NULL DEFAULT NULL,
    `a_id` INT(11) NULL DEFAULT NULL,
    `month` INT(11) NULL DEFAULT NULL
);

Data:

total | a_id | month
2     | 1    | 3
4     | 1    | 5
3     | 1    | 6
6     | 1    | 9
3     | 2    | 4
6     | 2    | 10


CREATE TABLE `months` (
    `month` INT(11) NULL DEFAULT NULL
);

Data: Just the values 1 through 12

What I want to achieve is to use the month as a timestamp, and for each a_id get their total for that month, with zero for the months that aren't set up (but only between the first month, and the last month, of that a_id).

My query so far:

SELECT c.month, COALESCE(d.total, 0) AS 'total', COALESCE(d.a_id, 1) AS 'a_id'
FROM (
    SELECT month
    FROM months
    WHERE month >= (SELECT MIN(month) FROM dummy WHERE a_id = 1)
    AND month <= (SELECT MAX(month) FROM dummy WHERE a_id = 1)
) c
LEFT outer JOIN (
    SELECT *
    FROM dummy
    WHERE a_id = 1
) d ON d.month = c.month
ORDER BY c.month;

And its output:

month | total | a_id
3     | 2     | 1
4     | 0     | 1
5     | 4     | 1
6     | 3     | 1
7     | 0     | 1
8     | 0     | 1
9     | 6     | 1

How can I change this query and get the output for every a_id in the table, without an external program like PHP change the ID every time?

Upvotes: 0

Views: 35

Answers (1)

Akina
Akina

Reputation: 42632

It seems you need

SELECT months.month, 
       COALESCE(dummy.total, 0) total, 
       a_ids.a_id
FROM months
CROSS JOIN ( SELECT DISTINCT a_id
             FROM dummy ) a_ids
LEFT JOIN dummy ON months.month = dummy.month
               AND a_ids.a_id = dummy.a_id
INNER JOIN ( SELECT MIN(month) min_month,
                    MAX(month) max_month
             FROM dummy ) borders ON months.month BETWEEN borders.min_month 
                                                      AND borders.max_month
ORDER BY a_ids.a_id, months.month;

or

SELECT months.month, 
       COALESCE(dummy.total, 0) total, 
       a_ids.a_id
FROM months
CROSS JOIN ( SELECT DISTINCT a_id
             FROM dummy ) a_ids
LEFT JOIN dummy ON months.month = dummy.month
               AND a_ids.a_id = dummy.a_id
INNER JOIN ( SELECT MIN(month) min_month,
                    MAX(month) max_month,
                    a_id
             FROM dummy
             GROUP BY a_id ) borders ON months.month BETWEEN borders.min_month 
                                                         AND borders.max_month
                                    AND a_ids.a_id = borders.a_id
ORDER BY a_ids.a_id, months.month;

fiddle

Upvotes: 2

Related Questions