CODEWITHSUNDEEP
mysqlsql
Vikram Deshmukh
Vikram Deshmukh

Reputation: 63

Performing aggregate on already grouped by aggregated SQL output in mysql

I have tables as below - 

Employee  - 
Employee_Id     Name        Limit_Amt
1               Sachin      3000
2               Mahi        2500

Employee_Wage  - 
Employee_Id     Amount      Pay_Date
1               200         2017-01-01
1               250         2017-02-01
1               300         2017-03-01
2               350         2017-01-01
2               400         2017-02-01
2               300         2017-03-01

Now to find out Remaining limit for individual employee below query works fine - 

SELECT e.Limit_Amt - SUM(Amount) AS 'Remaining Limit' 
FROM Employee e, Employee_Wage ew 
WHERE e.Employee_Id = ew.Employee_Id 
GROUP BY e.Employee_Id, e.Limit_Amt

It gives output as - 

Remaining Limit
2250
1450

But, further I wish to calculate Total of remaining limit (i.e. deriving 3700), then if I apply SUM(e.Limit_Amt - SUM(Amount)) ... it's not working.

Conceptually I am blocked. Could someone please guide me here? Thanks in advance.

Upvotes: 1

Views: 44

Answers (2)

Javlon Ismatov
Javlon Ismatov

Reputation: 194

select e.Name,e.Limit_Amt,sum(cast(w.Amount as int)) 'sum',
e.Limit_Amt-sum(cast(w.Amount as int)) 'Remaining'
from Employee e join Employee_Wage w
on e.Employee_Id=w.Employee_Id
group by e.Name,e.Limit_Amt

Upvotes: 0

Andomar
Andomar

Reputation: 238176

You could use a subquery:

SELECT  SUM(remaining)
FROM    (
        SELECT  e.Limit_Amt - SUM(Amount) AS remaining
        FROM    Employee e
        JOIN    Employee_Wage ew 
        ON      e.Employee_Id = ew.Employee_Id
        GROUP BY
                e.Employee_Id
        ,       e.Limit_Amt
        ) sub

The from a join b on a.id = b.id syntax is clearer than SQL92's from a, b where a.id = b.id.

Upvotes: 1

Related Questions