title: To get distinct month name and count the values of duplicates, among multiple tables in mysql
body: <p>I have three tables likely <code>tbdata</code>,<code>tbdatahw</code>,<code>tbdatamr</code>. In that all tables the fields called <code>status</code> and <code>c42</code> are same. for example <code>tbdatahw table</code><a href="https://i.stack.imgur.com/DekB1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DekB1.png" alt="enter image description here"></a></p>
  
  <p>like this same fields in <code>tbdata and tbdatamr table</code>. Field status 1 specifies green color and 2 for yellow and <code>c42</code> for date details. what i try achieve is in January month how many green yellow status in among all 3 tables. I tried this query in workbench</p>
  
  <pre><code>    SELECT  MONTHNAME(c42) MONTH, SUM( case when status = 0 THEN 1 else 0 end) AS red
  ,SUM( case when status = 1 THEN 1 else 0 end) AS green
  ,SUM( case when status = 2 THEN 1 else 0 end) AS yellow
  FROM  tbdata where  month(c42) is not null group by month(c42)
  union all 
  SELECT  MONTHNAME(c42) MONTH, SUM(distinct case when status = 0 THEN 1 else 0 end) AS red
  ,SUM(distinct case when status = 1 THEN 1 else 0 end) AS green
  ,SUM(distinct case when status = 2 THEN 1 else 0 end) AS yellow
  FROM  tbdatamr where  month(c42) is not null group by month(c42)
  union all 
  SELECT  MONTHNAME(c42) MONTH, SUM(distinct case when status = 0 THEN 1 else 0 end) AS red
  ,SUM(distinct case when status = 1 THEN 1 else 0 end) AS green
  ,SUM(distinct case when status = 2 THEN 1 else 0 end) AS yellow
  FROM  tbdatahw where  month(c42) is not null group by month(c42);
  </code></pre>
  
  <p>It is giving the output with redundancy like<a href="https://i.stack.imgur.com/QutCs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QutCs.png" alt="enter image description here"></a></p>
  
  <p>i don't want the February month coming two times but  i want to add the green status value from two feb into one.  I tried <code>distinct</code> and <code>union</code> as well but not working. I want the output like this<a href="https://i.stack.imgur.com/XPX2x.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XPX2x.png" alt="enter image description here"></a></p>