How to subtract the total of repetitions between two tables?

Question

How to subtract the total of repetitions between two tables?

Navigation

#1 by (1 votes)

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.

mysql

asked by sagitorr 19.12.2018 в 21:05

source

1 answer

Can I use an Eval () in the ItemCommand of a ListView? Delete an item from ListView and DB SQLite with OnItemLongClickListener

score 1 · Accepted Answer

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