CODEWITHSUNDEEP
sqlsql-server
C Deuter
C Deuter

Reputation: 933

Select sum from

I am trying to get the sum of all the rows associated with each customer and join on them.

However I am finding that if no rows exist it leaves out the customer completely.

I would prefer if the sum was zero. How would I achieve this.

Here is the SQL statement:

SELECT 
    Id, DebitSum
FROM
    Customers
JOIN
    (SELECT 
         SUM(Amount) DebitSum, CustomerId
     FROM 
         Purchases
     WHERE 
         Completed IS NULL
     GROUP BY 
         CustomerId) p ON p.CustomerId = Id;

Using SQL Server, if it matters.

Upvotes: 0

Views: 60

Answers (6)

maddy
maddy

Reputation: 50

try this SELECT c.Id, COALESCE(p.DebitSum, 0) as DebitSum FROM Customers c LEFT JOIN Purchases p on c.Id = p.CustomerId where p.completed is null

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use subquery only:

select id, (select coalesce(sum(p.Amount), 0) 
            from Purchases p 
            where p.CustomerId  = c.id and p.Completed IS NULL
            ) as DebitSum
from Customers c
group by id;

Upvotes: 1

Maria Clara
Maria Clara

Reputation: 13

SELECT SUM(Amount) DebitSum, CustomerId

there is no AS after the information? select sum(amount) as debitsum, customerid

Upvotes: 0

Brad
Brad

Reputation: 3616

Try this:

SELECT Id, DebitSum
FROM Customers
LEFT JOIN (
            SELECT SUM(Amount) DebitSum, CustomerId
            FROM Purchases
            WHERE Completed IS NULL
            GROUP BY CustomerId
       ) p ON p.CustomerId = Id;

Your doing a JOIN which means it has to exist in both tables/data sets. Changing it to LEFT JOIN only requires it to be in the First table and not the one after the LEFT JOIN

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Just use LEFT JOIN:

SELECT c.Id, COALESCE(p.DebitSum, 0)
FROM Customers c LEFT JOIN
     (SELECT SUM(p.Amount) as DebitSum, p.CustomerId
      FROM Purchases p
      WHERE p.Completed IS NULL
      GROUP BY CustomerId
     ) p
     ON p.CustomerId = c.Id;

This would normally be written without the subquery:

SELECT c.Id, COALESCE(SUM(p.Amount), 0) as DebitSum
FROM Customers c LEFT JOIN
     Purchases p
     ON p.CustomerId = c.Id;
WHERE p.Completed IS NULL
GROUP BY c.Id

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You could use LEFT JOIN:

SELECT Id, COALESCE(DebitSum,0) AS DebitSum
FROM Customers
LEFT JOIN (
            SELECT SUM(Amount) DebitSum, CustomerId
            FROM Purchases
            WHERE Completed IS NULL
            GROUP BY CustomerId
       ) p ON p.CustomerId = Id;

Upvotes: 1

Related Questions