CODEWITHSUNDEEP
sql-servert-sql
Suresh
Suresh

Reputation: 75

Distinct rows with Summation

I have EXPENSE table.

DECLARE @EXPENSE TABLE
(
    ID INT,
    CURRENCY NVARCHAR(4),
    AMOUNT   MONEY
)

INSERT INTO @EXPENSE VALUES
(1,'USD',100),
(2,'USD',10),
(1,'USD',80),
(2,'USD',5),
(1,'EUR',80),
(1,'EUR',30),
(3,'USD',20)

The data is shown below:

+----+----------+--------+
| ID | CURRENCY | AMOUNT |
+----+----------+--------+
| 1  | USD      | 100    |
+----+----------+--------+
| 2  | USD      | 10     |
+----+----------+--------+
| 1  | USD      | 80     |
+----+----------+--------+
| 2  | USD      | 5      |
+----+----------+--------+
| 1  | EUR      | 80     |
+----+----------+--------+
| 1  | EUR      | 30     |
+----+----------+--------+
| 3  | USD      | 20     |
+----+----------+--------+

I would like to get the result like this.

+----+----------+--------+
| ID | CURRENCY | AMOUNT |
+----+----------+--------+
| 1  | EUR      | 110    |
+----+----------+--------+
| 1  | USD      | 180    |
+----+----------+--------+
| 2  | USD      | 15     |
+----+----------+--------+
| 2  | EUR      | 0      |
+----+----------+--------+
| 3  | USD      | 20     |
+----+----------+--------+
| 3  | EUR      | 0      |
+----+----------+--------+

I wrote the following:

SELECT E.ID, E.CURRENCY, SUM(E.AMOUNT) AS AMOUNT
FROM @EXPENSE AS E
GROUP BY E.ID, E.CURRENCY

But it does not give me missing currency and zero amounts.

Can somebody please help?

Upvotes: 1

Views: 37

Answers (1)

forpas
forpas

Reputation: 164099

You need a CROSS join of the distinct ids and the distinct currencies and then a LEFT join to the table to aggregate:

SELECT i.ID, c.CURRENCY,
       COALESCE(SUM(AMOUNT), 0) TOTAL
FROM (SELECT DISTINCT ID FROM @EXPENSE) i
CROSS JOIN (SELECT DISTINCT CURRENCY FROM @EXPENSE) c
LEFT JOIN @EXPENSE e ON e.ID = i.ID AND e.CURRENCY = c.CURRENCY
GROUP BY i.ID, c.CURRENCY
ORDER BY i.ID, c.CURRENCY

See the demo.
Results:

> ID | CURRENCY |    TOTAL
> -: | :------- | -------:
>  1 | EUR      |    110
>  1 | USD      |    180
>  2 | EUR      |      0
>  2 | USD      |     15
>  3 | EUR      |      0
>  3 | USD      |     20

Upvotes: 1

Related Questions