jrobles Posted December 14, 2009 Share Posted December 14, 2009 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. Link to comment https://forums.phpfreaks.com/topic/185113-join-help/ Share on other sites More sharing options...
cags Posted December 14, 2009 Share Posted December 14, 2009 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 Link to comment https://forums.phpfreaks.com/topic/185113-join-help/#findComment-977148 Share on other sites More sharing options...
jrobles Posted December 14, 2009 Author Share Posted December 14, 2009 Thats the UserID for the sales associate. I basically need all sales under that UserID Link to comment https://forums.phpfreaks.com/topic/185113-join-help/#findComment-977158 Share on other sites More sharing options...
PFMaBiSmAd Posted December 14, 2009 Share Posted December 14, 2009 You need to add - GROUP BY Clients.ClientID Link to comment https://forums.phpfreaks.com/topic/185113-join-help/#findComment-977163 Share on other sites More sharing options...
jrobles Posted December 14, 2009 Author Share Posted December 14, 2009 sweet, that worked! Thanks a million buddy! Link to comment https://forums.phpfreaks.com/topic/185113-join-help/#findComment-977167 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.