CODEWITHSUNDEEP
phpmysql
villoui
villoui

Reputation: 245

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)

Upvotes: 0

Views: 32

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 1

Related Questions