CODEWITHSUNDEEP
mysql
Sri
Sri

Reputation: 27

Count from two tables and display the counts in the same table in MYSQL

I have two tables with a primary key and a column(name color)

Table1
|Color| 
|red  |
|green|
|blue |

Table2
|Color| 
|red  |
|red  | 
|green|

I tried 

SELECT  (
        SELECT COUNT(*)
        FROM   table1
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   table2
    ) AS count2

but this gives 

count1|count2| 
 3    |3     |

How can i get an output grouped with the color like,

|Color|count1| count2
|red  | 1    |2
|green| 1    |1
|blue |1     |0

Upvotes: 0

Views: 60

Answers (2)

Vijaya Vignesh Kumar
Vijaya Vignesh Kumar

Reputation: 444

Hi sri simply you can use union and group by

select id,color, sum(coun_t) as counts from (
    (SELECT id,color,count(*) as coun_t FROM `table1` group by color)
          union 
    (SELECT id,color,count(*) as coun_t FROM `table2` group by color)
 ) as p group by color order by id asc

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35573

SELECT
      color
    , MAX(CASE WHEN tabl = 'T1' THEN count_of END) count1
    , MAX(CASE WHEN tabl = 'T2' THEN count_of END) count2
FROM (
      SELECT 'T1' tabl, Color, COUNT(*) count_of FROM table1 GROUP BY Color
      UNION ALL
      SELECT 'T2' tabl , Color , COUNT(*)        FROM table2 GROUP BY color
     ) d
GROUP BY color

Upvotes: 1

Related Questions