# How to subtract the total of repetitions between two tables?

2

Good morning,

I have this query that brings me the total of the times that the user repeats the previous month of the "megusta" table, in order to get the total per user of how many I like to have obtained the previous month.

SELECT usuario, COUNT(1) AS total
FROM megusta tp WHERE YEAR(fecha) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(fecha) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY tp.usuario HAVING COUNT(1) > 1

But I want to add that I subtract the total number of times repeated from the table "nomegusta" and thus get a specific total of "nomegusta" VS "megusta" and if the total is greater than or equal to 50 that you insert in the table "Rewards" users who met that condition.

I really do not have much experience in MYSQL and I already try several times how to do it but it does not work out, and I also wanted to know if what I need can be put in a query?

I add how my tables are composed:

megusta

id, idRespuesta, usuario, fecha

nomegusta

id, idRespuesta, usuario, fecha

awards

id, usuario, fecha_inicio, fecha_fin < - this field would insert a date at 30 days from the start date, that is why when the 50 condition is fulfilled, it would insert the users in this table.

asked by sagitorr 19.12.2018 в 21:05
source

1

Knowing the structure of the tables, could give you a better answer. Even without knowing this and understanding that:

• You have a table megusta and% nomegusta
• Both have a similar structure and the same criteria to select them

You could make two queries and join them with a union , the only particularity that we would consider the amount of nomegusta in negative: COUNT(1) * -1 AS 'total' , so as to be able to "net them":

SELECT  T.usuario,
SUM(T.total) AS 'total_neto'
FROM (      SELECT  usuario,
COUNT(1) AS 'total'
FROM megusta
WHERE   YEAR(fecha) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(fecha) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY usuario HAVING COUNT(1) > 1

UNION

SELECT  usuario,
COUNT(1) * -1  AS 'total'
FROM nomegusta
WHERE   YEAR(fecha) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(fecha) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY usuario HAVING COUNT(1) > 1
) T
GROUP BY T.usuario
HAVING SUM(T.total) >= 50