mysql inner join two tables with month matching-CodePudding

I have a two tables in MySQL and both tables in same database. table name data

| service   | count     | date | 
--------------------------------------
| bugss     | 375   | 2022-01-01 05:00:00.00000 
| fromsite  | 5     | 2022-02-01 05:00:00.00000 
| kbocetra  | 100   | 2022-01-05 07:00:00.00000

tried for data table

SELECT SUM(`count`) AS Alertcount,
DATE_FORMAT(`date`, '%M') AS Month,
FROM data
GROUP BY DATE_FORMAT(`date`, '%Y-%m')

output:

January  | 475
February | 5

another table name pd

| group | minutes   | projdate | 
-------------------------------- 
gcp     | 145       | 2022-01-01 05:00:00.00000 
azure   | 10        | 2022-02-01 05:00:00.00000 
aws     | 80        | 2022-01-05 07:00:00.00000

i tried below command for separate tables, for pd table as below ..which gives output as

SELECT SUM(`minutes`) AS Hours,
DATE_FORMAT(`group  `, '%M') AS Month
FROM pd
GROUP BY DATE_FORMAT(`group`, '%Y-%m')

output:

January  | 225
February | 10

and im expected the ouput like below, and total count would be as output of two tables count/minutes i.e., 475/225 and 5/10. please help, i red about inner statement, but didn't worked.

Month	total
January	0.78
February	2

CodePudding user response：

Run the following command and see the results.

SELECT
    a.`Month`,
    a.`Hours` / b.`Alertcount` as 'total'
FROM
    (
SELECT
    SUM( `minutes` ) AS Hours,
    DATE_FORMAT( `group  `, '%M' ) AS 'Month' 
FROM
    pd 
GROUP BY
    DATE_FORMAT( `group`, '%Y-%m' ) 
    ) a
    INNER JOIN (
SELECT
    SUM( `count` ) AS Alertcount,
    DATE_FORMAT( `date`, '%M' ) AS 'Month' 
FROM
DATA 
GROUP BY
    DATE_FORMAT( `date`, '%Y-%m' ) 
    ) b ON a.`Month` = b.`Month`

CodePudding user response：

When selecting the tables you can use the division operator as you can see here.