Try adding DISTINCT to your query: SELECT DISTINCT ....
I'm not sure if that's going to work in your case, but the problem you are having seems to be duplicate rows caused by joins. A left join will always return one or more rows from the main table (VPN).
On Jul 30, 2004, at 9:47 AM, Alex wrote:
I'm having a problem with summing up joined tables.. can anyone help me?
I have 3 tables that I am joining together
vpn, dialup, and userinfo
vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_time and other extraneous columns..
dialup has the following columns user_id session_time and other extraneous columns..
userinfo has the following columns id username first_name last_name and other extraneous columns..
dialup user_id joins with userinfo's id to get the username
My query is as follows..
SELECT userinfo.First_Name, userinfo.Last_Name, (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, (Sum(VPN.Session_Time)/3600) as VPN_Total_hours FROM VPN LEFT JOIN user_info ON (VPN.Username = userinfo.Username) LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) WHERE GROUP BY Username, First_Name, Last_Name
If I don't do any joining.. and sum up session time by itself i get the correct # of hours. With this joining I get roughly a factor of 10x the correct amount.. Why is this the case? Can anyone help me fix it? I couldn't find any advanced sql query building topics on this through google, so I'm trying my luck here.
thanks Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
