Performing aggregate on already grouped by aggregated SQL output in mysql

Question

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.

score 1 · Answer 1 · answered Aug 16 '17 at 13:36

1

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.

answered Aug 16 '17 at 13:36

Andomar

232,371
49
380
404

To add to the last line. @Andomar is replacing your [implicit join for explicit join](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins), the ANSI standard added 25 years ago. Equivalent performance but clearer, more maintainable code. – Parfait Aug 16 '17 at 17:01
Thanks your response helped me. – Vikram Deshmukh Aug 17 '17 at 04:09

score 0 · Answer 2 · answered Aug 16 '17 at 13:45

0

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

answered Aug 16 '17 at 13:45

Javlon Ismatov

194
12

Don't think you can nest sums like that? – Andomar Aug 16 '17 at 13:50
says "Invalid use of group function" when I [test it](http://rextester.com/NHB56232) – Andomar Aug 16 '17 at 14:31
I'm using MS SQL Server 2012 – Javlon Ismatov Aug 16 '17 at 14:35
Ah I see, the OP is looking for "Total of remaining limit (i.e. deriving 3700)," I (suppose over all employees), you are calculating the total amount **per** employee – Andomar Aug 16 '17 at 15:50

Performing aggregate on already grouped by aggregated SQL output in mysql

2 Answers2