Hi list Could anyone pls advise here?
I have 2 tables, p_details and s_details with the field quantity. The common field is project_no Now, i would like to take the sum of quantity in both the tables (inner join on project_no) I used this: sql = "" sql = sql & " SELECT" sql = sql & " Sum(P_DETAILS.QUANTITY) AS P_SUM," sql = sql & " Sum(S_DETAILS.QUANTITY) AS S_SUM, P_DETAILS.PROJECT_NO" sql = sql & " FROM P_DETAILS INNER JOIN S_DETAILS ON P_DETAILS.PROJECT_NO = S_DETAILS.PROJECT_NO" sql = sql & " GROUP BY P_DETAILS.PROJECT_NO" sql = sql & " ORDER BY P_DETAILS.PROJECT_NO DESC" Lets take an eg: ------------- 1) p_details.project_no = 1005, p_details.quantiuty = 500 2) s_details.project_no = 1005, s_details.quantiuty = 100 3) s_details.project_no = 1005, s_details.quantiuty = 300 Logically i should get P_SUM = 500 and S_SUM = 400. But, i get P_SUM = 1000 and S_SUM = 400 which is wrong. So, my P_SUM is multiplied by number of times of s_details records. How can i manage to write this in SQL without using SUB queries? Thanks Ilyas [Non-text portions of this message have been removed] --------------------------------------------------------------------- Home : http://groups.yahoo.com/group/active-server-pages --------------------------------------------------------------------- Post : [email protected] Subscribe : [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] --------------------------------------------------------------------- Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/active-server-pages/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
