This has more to do with how JOINing tables work than making the sums work
right.
When you join tables, a new virtual table containing all possible
combinations of rows from each of the tables is created. The ON clauses of
each join limits which rows stay in the table (or get put into it in the
first place, depending on your point of view). What is happening is that
for each VPN/user_info combination there are multiple dialup rows that
also match. That means there will be duplicates of all of your data.
Those duplicates are throwing your sums off;
If you want to present multiple sums in the same report, you have to first
calculate them in isolation (to avoid the duplication problems I just
described) then combine them later to create a well-formatted report.
CREATE TEMPORARY TABLE tmpDialup
SELECT
userinfo.Username,
userinfo.First_Name,
userinfo.Last_Name,
(Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours,
FROM userinfo
LEFT JOIN Dialup
ON (userinfo.ID = dialup.User_ID)
GROUP BY Username, First_Name, Last_Name
CREATE TEMPORARY TABLE tmpVPN
SELECT
userinfo.Username,
userinfo.First_Name,
userinfo.Last_Name,
(Sum(VPN.Session_Time)/3600) as VPN_Total_hours
FROM userinfo
LEFT JOIN VPN
ON (VPN.Username = userinfo.Username)
GROUP BY Username, First_Name, Last_Name
SELECT
u.Username
, u.First_name
, u.Last_Name
, SUM(Dialup_Total_Hours) as Dialup_Total_Hours
, SUM(VPN_Total_hours) as VPN_Total_Hours
FROM userinfo u
INNER JOIN tmpDialup td
ON td.Username =u.Username
AND td.First_Name = u.First_Name
AND td.Last_Name = u.Last_Name
INNER JOIN tmpVPN tv
ON tv.Username =u.Username
AND tv.First_Name = u.First_Name
AND tv.Last_Name = u.Last_Name
GROUP BY u.Username, u.First_name, u.Last_Name
DROP TABLE tmpDialup, tmpVPN;
IF Username is a unique value on the table userinfo, you can get rid of
all of the First_Name and Last_Name columns from all of the queries except
the last one. They would have to stay in the SELECT and ORDER BY clauses
(but that's all) if you want to see them in the report.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Alex <[EMAIL PROTECTED]> wrote on 07/30/2004 09:47:20 AM:
> 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]
>
