mysql join or concat query from two table to get month wise registration

Question

I have two tables reg_dealer and claim_data .

SELECT YEAR(claim_dt) AS `Year`, 
       MONTHNAME(claim_dt) AS `Month`, 
       COUNT(distinct `uniquecode`) AS `No of Reg` 
FROM claim_data
WHERE YEAR(claim_dt) = '2016' 
GROUP BY `Year`, `Month` 
ORDER BY MONTH(claim_dt) DESC


SELECT YEAR(reg_date) AS `Year`, 
       MONTHNAME(reg_date) AS `Month`, 
       COUNT(*) AS `No of Reg`
FROM reg_dealer 
WHERE YEAR(reg_date) = '2016' 
GROUP BY `Year`, `Month` 
ORDER BY MONTH(reg_date)  DESC

i am getting month wise result from both table in same format.

Year----------Month----------No_of_Reg
2016          March           150
2016          February        125
2016          Janurary         75

and i want registration and claim in a one go

Year----------Month----------No_of_Reg---------No_of_claims
2016          March           150                350
2016          February        125                250
2016          Janurary         75                150

also want to get the unique claimants

please help

Join the two queries on the year and month. – Barmar Mar 08 '16 at 16:58 — Barmar, Mar 08 '16 at 16:58

Barmar · Accepted Answer · 2016-03-08T17:15:27.123

0

Join the queries.

SELECT t1.Year, t1.Month, `No of Reg`, `No of Claims`
FROM (SELECT YEAR(claim_dt) AS `Year`, MONTHNAME(claim_dt) AS `Month`, MONTH(claim_dt) AS MonthNum, COUNT(distinct `uniquecode`) AS `No of Claims` 
        FROM claim_data 
        WHERE YEAR(claim_dt) = '2016' GROUP BY `Year`, `Month`) AS t1
JOIN (SELECT YEAR(reg_date) AS `Year`, MONTH(claim_dt) AS MonthNum, COUNT(*) AS `No of Reg` 
        FROM reg_dealer 
        WHERE YEAR(reg_date) = '2016' 
        GROUP BY `Year`, `MonthNum`) AS t2
    ON t1.Year = t2.Year AND t1.MonthNum = t2.MonthNum
ORDER BY t1.Year, t1.MonthNum DESC

edited Mar 08 '16 at 17:15

answered Mar 08 '16 at 17:01

Barmar

741,623
53
500
612

thank for the answer , but query not executed, getting error Unknown column 't2.Month' in 'on clause' and i replace t2.month to t2.MonthNum but no result – user2751498 Mar 08 '16 at 17:11
Sorry, meant to change that to `MonthNum`. – Barmar Mar 08 '16 at 17:15
but sir query got the message Your SQL query has been executed successfully (Query took 0.0126 sec) but no result – user2751498 Mar 08 '16 at 17:20
There will only be results for months that have both claims and registrations. – Barmar Mar 08 '16 at 17:22
works now! script is working but no of claim output is not exactly like my query is written – user2751498 Mar 08 '16 at 17:22
How is it different? – Barmar Mar 08 '16 at 17:24
If you need to get months that aren't in both tables, you need a full outer join. MySQL doesn't have this operation, see http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql for workarounds. – Barmar Mar 08 '16 at 17:24

mysql join or concat query from two table to get month wise registration

1 Answers1