I have 2 selects that just get the SUM of IDs and are grouped by month.. I would like to have them only on 1 SELECT - side by side...

SELECT MONTH(data) AS month, COUNT(id) AS TOTAL
FROM numeracao
WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY MONTH(DATA);

the 2nd select:

SELECT MONTH(dia) AS month, COUNT(id) AS TOTAL
FROM revisoes
WHERE dia BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY MONTH(dia)

The rewsult was to be expected somethinig like this:

where:

• month is from 1 to 12
• NUM is the sum of query 1 on table numeracao
• REV is the sum of query 2 on table revisoes

CodePudding user response：

You can do this by using union and then an outer aggregation:

select Month, Max(Num) Num, Max(Rev) Rev
from (
  select Month(data) as month, Count(id) Num, null Rev
  from numeracao
  where data between '2023-01-01' and '2023-12-31'
  group by Month(DATA)
  union all
  select Month(dia) as month, null Num, Count(id) Rev
  from revisoes
  where dia between '2023-01-01' and '2023-12-31'
  group by Month(dia)
)t;

CodePudding user response：

You can apply the inner join on the results of the aggregations to make counts appear on the same row for each month.

SELECT numtab.month, 
       numtab.num, 
       revtab.rev
FROM       (SELECT MONTH(data) AS month, COUNT(id) AS num
            FROM numeracao
            WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
            GROUP BY MONTH(DATA))                         numtab 
INNER JOIN (SELECT MONTH(dia) AS month, COUNT(id) AS rev
            FROM revisoes
            WHERE dia BETWEEN '2023-01-01' AND '2023-12-31'
            GROUP BY MONTH(dia))                          revtab
        ON numtab.month = revtab.month

Computing the aggregation after the inner join would bring to duplicate rows and incorrect output, given than the two tables most likely have a n-to-n association.

If you're not ensured to have at least one value for all months on both tables, you should have a month calendar table and use left join instead.

SELECT calendar.month, 
       numtab.num, 
       revtab.rev
FROM      (SELECT 1 AS month UNION ALL
           SELECT 2 AS month UNION ALL
           SELECT 3 AS month UNION ALL
           SELECT 4 AS month UNION ALL
           SELECT 5 AS month UNION ALL
           SELECT 6 AS month UNION ALL
           SELECT 7 AS month UNION ALL
           SELECT 8 AS month UNION ALL
           SELECT 9 AS month UNION ALL
           SELECT 10 AS month UNION ALL
           SELECT 11 AS month UNION ALL
           SELECT 12 AS month           ) calendar
LEFT JOIN (SELECT MONTH(data) AS month, COUNT(id) AS num
           FROM numeracao
           WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
           GROUP BY MONTH(DATA)) numtab 
       ON calendar.month = numtab.month
LEFT JOIN (SELECT MONTH(dia) AS month, COUNT(id) AS rev
           FROM revisoes
           WHERE dia BETWEEN '2023-01-01' AND '2023-12-31'
           GROUP BY MONTH(dia)) revtab
        ON calendar.month = revtab.month

I wouldn't recommend this one anyways if your problem gets solved with the former query.

CodePudding user response：

SELECT month,TOTAL FROM(SELECT MONTH(data) AS month, COUNT(id) AS TOTAL
FROM numeracao
WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY MONTH(DATA)
UNION ALL
SELECT MONTH(dia) AS month, COUNT(id) AS TOTAL
FROM revisoes
WHERE dia BETWEEN '2023-01-01' AND '2023-12-31' )as rows
GROUP BY month