I have two tables: Sales and history. In the sales table this is practically what the client owes, and in history what he has already paid for.
I want to make a query
that tells me which product is the most consumed. For this I have to go to the two tables and then add by idProducto
.
I've been trying with this query:
select COUNT(h.idProduct) as count, p.name as productName FROM History h
INNER JOIN Product p ON p.idProduct = h.idProduct
GROUP BY productName
UNION
select COUNT(s.idProduct) as count, p.name as productName FROM Sale s
INNER JOIN Product p ON p.idProduct = s.idProduct
GROUP BY productName ORDER BY count DESC
But the problem is that it returns me the following:
And I should return only two records: Coca-Cola Lata Zero = 13 y Coca-Cola Lata Sabor Original = 38
...
How do I manage to immerse myself in these values?