Skip to content
Advertisement

MySQL combine counts on columns from two tables

Is there a way to combine these two queries into one where table1.column1 = table2.column1 so that it would return the following?

select column1, count(column1) as var1 from table1 where  datediff(now(),createdAt) = 0 group by column1

select column1, count(column1) as var2 from table2 where  datediff(now(),createdAt) = 0 group by column1

column1, var1, var2
column1, var1, var2
column1, var1, var2
column1, var1, var2

Advertisement

Answer

Use subquerys and INNER JOIN

SELECT 
    a.column1, a.var1, b.vae2
FROM
    (SELECT 
        column1, COUNT(column1) AS var1
    FROM
        table1
    WHERE
        DATEDIFF(NOW(), createdAt) = 0
    GROUP BY column1) a
        INNER JOIN
    (SELECT 
        column1, COUNT(column1) AS var2
    FROM
        table2
    WHERE
        DATEDIFF(NOW(), createdAt) = 0
    GROUP BY column1) b ON a.column1 = b.column1;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement