villoui villoui - 6 months ago 16
MySQL Question

How to count the mysql results of 2 tables that have similar conditions?

I want to insert into the

goals
column of the
pivot_data
the count of 2 tables in total.

I can make it work with 1 table, but I am not able to combine the second table on this. What am I missing?

INSERT INTO pivot_data (date, goals)
SELECT CURDATE( ) AS today, COUNT( * ) AS goals


then

FROM alerts_data
WHERE DATE( alerts_data_timestamp ) = CURDATE( ) AND alerts_data_status ='goal'


but I also want to combine

FROM alerts_push_data
WHERE DATE( push_data_timestamp ) = CURDATE( ) AND push_data_status ='goal'


and then

ON DUPLICATE KEY UPDATE pivot_data.goals = VALUES(goals)

Answer

Try using UNION ALL:

INSERT INTO pivot_data (date, goals)
SELECT today, goals
FROM (    
  SELECT CURDATE( ) AS today, SUM( goals ) AS goals
  FROM (
    SELECT COUNT(*) AS goals
    FROM alerts_data
    WHERE DATE( alerts_data_timestamp ) = CURDATE( ) 
          AND alerts_data_status ='goal'

    UNION ALL

    SELECT COUNT(*) AS goals
    FROM alerts_push_data
    WHERE DATE( push_data_timestamp ) = CURDATE( ) 
          AND push_data_status ='goal') AS s ) AS t
ON DUPLICATE KEY UPDATE pivot_data.goals = t.goals