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 ta开发者_如何学编程bles:

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.

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

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

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.

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

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

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