JOIN HELP

[jrobles]

Im having a problem getting a sql statement to give me the data that i need. Here is my statement

 

SELECT 
Clients.ClientID, 
ClientProfile.FirstName,
ClientProfile.LastName, 
SUM(OrderAmount) AS TOTAL
FROM Clients
JOIN ClientProfile ON Clients.ClientID = ClientProfile.ClientID
LEFT JOIN OrderHeader ON Clients.ClientID = OrderHeader.ClientID
WHERE Clients.UserID =17

 

The code above only returns one Client row with the sum of all the rows in my orderheader table. I need each client listed with the sum of of their orders only in a table format i.e.

 

CLIENTID---FNAME---LNAME---TOTAL

4                John      Doe        57.87

5                Jane      Smith    24.25

6                Steve    Meh        14.85

 

Here is a brief description of what I am doing. I have sales associates that can have many clients, each client can have many orders. I need to report all the clients and their total sales for any given sales associate.

 

[cags]

Of course it only returns one row, you tell it too...

 

WHERE Clients.UserID =17

 

If the one row you are getting is correct for that user, just removeing the WHERE clause should give you the right values... Also what is the difference between Clients.ClientID and Clients.UserID

[jrobles]

Thats the UserID for the sales associate. I basically need all sales under that UserID

[PFMaBiSmAd]

You need to add -

 

GROUP BY Clients.ClientID

[jrobles]

sweet, that worked! Thanks a million buddy!