Tengo dos tablas con una clave principal y una columna (color del nombre)

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

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

Lo intenté

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

Pero esto da

count1|count2| 
 3    |3     |

¿Cómo puedo obtener una salida agrupada con el color como,

|Color|count1| count2
|red  | 1    |2
|green| 1    |1
|blue |1     |0
0
Sri 15 nov. 2017 a las 13:11

2 respuestas

La mejor respuesta
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
1
Used_By_Already 15 nov. 2017 a las 10:22

Hola sri, simplemente puedes usar union y 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
1
Vijaya Vignesh Kumar 15 nov. 2017 a las 10:25