CODEWITHSUNDEEP
sqlt-sql
JF-Mechs
JF-Mechs

Reputation: 1081

How to format a result set with multiple aggregate?

Given that I have the following table:

+--------+------------+------+--------+
| Amount |    Date    | Type | UserId |
+--------+------------+------+--------+
|      8 | 2018-08-10 | VL   |     11 |
|      8 | 2018-08-11 | VL   |     11 |
|      8 | 2018-08-12 | VL   |     11 |
|      4 | 2018-08-09 | EL   |      9 |
|      8 | 2018-08-10 | SL   |      9 |
+--------+------------+------+--------+

How can we format the result set to get the sum of the amount of the same type? as well as the sum of the amount of all the record which has past (Date > CurrentDate).

Something like this:

 +------+---------------------+----------------+
| Type | TotalScheduledLeave | TotalLeaveUsed |
+------+---------------------+----------------+
| VL   |                  24 |              0 |
| VL   |                  24 |              0 |
| VL   |                  24 |              0 |
| EL   |                  12 |              4 |
| SL   |                  12 |              4 |
+------+---------------------+----------------+

I have tried the following code:

SELECT Type
       ,SUM(Amount) as 'TotalScheduledLeave'
       ,CASE WHEN Date > GETUTCDATE()
       THEN SUM(Amount)
       ELSE 0
       END as 'TotalLeaveUsed'
FROM TimeOffTable
WHERE User = @UserId
GROUP BY Type

This throws an error because the date in the select list is not contained in an aggregate function or group clause. But when I tried to group the Date I'm getting the wrong value in 'TotalScheduledLeave'

Upvotes: 0

Views: 44

Answers (1)

AB_87
AB_87

Reputation: 1156

Try this:

SELECT   Type ,
         SUM(Amount) AS 'TotalScheduledLeave' ,
         SUM(CASE WHEN [Date] > GETUTCDATE() THEN amount
                  ELSE 0
             END) AS 'TotalLeaveUsed'
FROM     TimeOffTable
WHERE    USER = @UserId
GROUP BY Type;

Upvotes: 2

Related Questions