Question 63758 in PhpMyAdmin Question posted in
The official documentation can be found here.

mysql inner join two tables with month matching – Phpmyadmin

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
Tags:

2

Answers


  1. 0

    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`
    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search