CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000aggregate-functions
itwb
itwb

Reputation: 437

Table join including rows that do not have an entry - SUM()

I'm having a little bit of difficulty joining two tables together, involving a SUM which are not included in the second table.

I.e. I have two tables:

tblClient

 ID|FirstName|LastName
 1 |Billy    |Blogs
 2 |Bobby    |Biggs
 3 |Hill     |Bill
 4 |Super    |Man

tblComplete

 PurchaseID|ID|Amount
 123123    |1 |26.00
 43254     |2 |22.00
 546275    |3 |15.00
 463565    |3 |15.00
 343252    |1 |56.00

What I would like to do is join these two tables together, so that I get an output of:

 ID|FirstName|LastName| SUM(Amount)
 1 |Billy    |Blogs   | 82.00
 2 |Bobby    |Biggs   | 22.00
 3 |Hill     |Bill    | 30.00
 4 |Super    |Man     | 0

I would like Super Man to be included in the output, with a sum of zero, because he didn't purchase anything. How would I go about doing this?

EDIT: Primary key in tblComplete table.

Upvotes: 1

Views: 412

Answers (6)

Pankaj
Pankaj

Reputation: 10095

Using Aggregate Functions in the Select List

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum
FROM 
  tblClient as CLIENT 
  LEFT JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

OR

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum 
FROM 
  tblClient as CLIENT 
  Inner JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

Upvotes: 1

sachin
sachin

Reputation: 1457

select a.ID,a.FirstName,a.LastName, sum(b.Amount)  from tblClient a , tblComplete b where a.ID = b.ID group by b.ID

Upvotes: 1

Brian Webster
Brian Webster

Reputation: 30855

SQL Server:

You start your statement with a select from tblClient, which contains one record for each record in your output. Then you LEFT JOIN, you perform a grouping on ClientID and utilize the aggregate function SUM() that causes each client's amount(s) (if they exist at all) to be summed up. 

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum 
FROM 
  tblClient as CLIENT 
  LEFT JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

Upvotes: 3

Exos
Exos

Reputation: 3988

Try whit LEFT JOIN ?

SELECT
    tblClient.*,
    SUM(tblComplete.amount) as Amount
FROM
    tblClient
LEFT JOIN
    tblComplete ON tblClient.ID = tblComplete.ID
GROUP BY
    tblClient.id

If this no work (i don't execute it), try:

SELECT
    tblClient.*,
    IF(COUNT(tblComplete.*), SUM(tblComplete.amount), 0) as Amount
FROM
    tblClient
LEFT JOIN
    tblComplete ON tblClient.ID = tblComplete.ID
GROUP BY
    tblClient.id

Upvotes: 1

colinross
colinross

Reputation: 2085

Simple answer, use an OUTER JOIN, or LEFT JOIN (since LEFT JOINS are outer to begin with..)

See more: http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins

The keyword outer makes is include rows even if they don't have a record in both tables. LEFT or RIGHT is really just which side of the = the null-able table is on.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select
  CL.ID,
  CL.FirstName,
  CL.LastName,
  coalesce(CO.SumAmount, 0) as [SUM(Amount)]
from tblClient as CL
  left outer join
    (
      select
        sum(Amount) as SumAmount,
        ID
      from tblComplete
      group by ID
    ) as CO
    on CL.ID = CO.ID

Upvotes: 6

Related Questions