mercredi 6 janvier 2016

SQL - using MAX(SUM(COLUMN))

I am trying to build a query which gives me some users having max of sum of one field. I can get the max by limit 1. But as there can be possibility of having the same value of max for multiple users how can I achieve this?

For example I have table as ID, CONTACT_ID, ..., AMOUNT, ....

Currently I am using this kind of query:

SELECT SUM(AMOUNT) AS total,CONTACT_ID 
    FROM TABLE 
    WHERE ID = 1  
    GROUP BY CONTACT_ID     
    ORDER BY total DESC limit 1

If I try to use MAX() function. It gives me all the rows instead of max.

SELECT total,CONTACT_ID FROM
(SELECT SUM(AMOUNT) AS total,CONTACT_ID 
    FROM TABLE 
    WHERE ID = 1  
    GROUP BY CONTACT_ID     
    ORDER BY total DESC)
GROUP BY CONTACT_ID
HAVING total = MAX(total)

Aucun commentaire:

Enregistrer un commentaire