October 24, 2012

group by,order by sum value together

Table Structure and Data

employer_id    salary

111              1000
112              2000
113              1000
111              1000
112              5000
113              2000
111              1000
113              1000


this is just an example for understanding purpose..


so here we have 3 employers,employe(111) got salary 3 times,employe(112) got salary 2times and employe(113) got salary 3 times

so we are going to sort the list by who got maximum money

SQL Query


SELECT sum( salary ) AS amt , employ_id FROM TABLE GROUP BY employer_id ORDER BY amt DESC

 
SAMPLE OUTPUT

amt     employer_id
7000   112
4000   113
3000   111
No matter howmany times the employe got salary just we are listing out based on highest earner..