CODEWITHSUNDEEP
sql-servergroupingsql-server-2014
Jake
Jake

Reputation: 604

Need to "group" records but I don't have an aggregate function

I have a table called "Payments" as follows:

+----+--------+
| ID | Amount |
+----+--------+
|  1 | 1.00   |
|  2 | 3.00   |
|  3 | -15.00 |
|  3 | 20.00  |
+----+--------+

"Amount" represents cash that changes hands - a record with a positive amount indicates cash taken in and a record with a negative amount indicates cash returned as change.

The following query:

SELECT ID
     , CASE WHEN Amount >= 0 THEN Amount END AS CashTaken
     , CASE WHEN Amount < 0 THEN Amount END AS CashBack  
  FROM Payments
 WHERE TicketID IN ('1', '2', '3')

...gives me this result set:

+----+-----------+----------+
| ID | CashTaken | CashBack |
+----+-----------+----------+
|  1 | 1.00      | NULL     |
|  2 | 3.00      | NULL     |
|  3 | NULL      | -15.00   |
|  3 | 20.00     | NULL     |
+----+-----------+----------+

How can I group these records by ID so that I can get the following?

+----+-----------+----------+
| ID | CashTaken | CashBack |
+----+-----------+----------+
|  1 | 1.00      | NULL     |
|  2 | 3.00      | NULL     |
|  3 | 20.00     | -15.00   |
+----+-----------+----------+

I'm a little embarrassed, because I feel like this is something very simple that is right in front of my face.

Upvotes: 2

Views: 36

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

Can you try with MIN, MAX:

SELECT TicketID 
     , MAX(CASE WHEN Amount >= 0 THEN Amount END) AS CashTaken
     , MIN(CASE WHEN Amount < 0 THEN Amount END) AS CashBack  
FROM Payments
WHERE TicketID IN ('1', '2', '3')
GROUP BY TicketID

Sample execution with given data:

DECLARE @Payments TABLE (TicketID INT, Amount DECIMAL(5,2));

INSERT INTO @Payments(TicketID, Amount) 

SELECT 1, 1.00 UNION
SELECT 2, 3.00 UNION
SELECT 3, -15.00 UNION
SELECT 3, 20.00;

SELECT TicketID
     , MAX(CASE WHEN Amount >= 0 THEN Amount END) AS CashTaken
     , MIN(CASE WHEN Amount < 0 THEN Amount END) AS CashBack  
FROM @Payments
WHERE TicketID IN ('1', '2', '3')
GROUP BY TicketID

Result:

TicketID    CashTaken   CashBack
---------------------------------
1           1.00       NULL
2           3.00       NULL
3           20.00     -15.00

Upvotes: 2

Related Questions