FERNANDO ORTIZ FERNANDO ORTIZ - 1 month ago 10
SQL Question

select users, then sum and count that user

I have these two query, the first I look for the users, who have transactions in the last two months the second I sum and tell these users, but with all transactions of all time, the question is how do to assemble this query, first I look and then sum and tell?

SELECT DISTINCT(id_ju1) from master where fecha_registro > DATE_SUB(NOW(), INTERVAL 2 MONTH)

SELECT sum(precio) / count(DISTINCT(id_ju1)) AS TOTAL FROM master

Answer

You can do it in one query:

SELECT sum(m.precio)  / count(DISTINCT(m.id_ju1)) AS TOTAL FROM master m 
WHERE EXISTS (SELECT * FROM master m1 WHERE m1.fecha_registro > DATE_SUB(NOW(), INTERVAL 2 MONTH) 
AND m.id_ju1=m1.id_ju1)
Comments