MS Sql: Sum with inner join Sum problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

MS Sql: Sum with inner join Sum problem

Author

Topic

jebrown55
Starting Member

3 Posts

Posted - 2009-02-11 : 11:15:27

I have a table with an inner join to another table. I need to get the sum's from all of the fields that I have collected in the main table (Time_Allocation) including the sum in the related table (PR_time). I am getting the correct information from the joined table (PR_time). However, on the main table (Time_Allocation) I am getting a multiple of however many is in the sub-table. I'm stumped on this please help.

My query:
SELECT Sum(Time_Allocation.ng_pqprocessing) AS ng_pqprocessing, Sum(Time_Allocation.ng_mqprocessing) AS ng_mqprocessing, Sum(Time_Allocation.ng_sales_asst) AS ng_sales_asst, Sum(Time_Allocation.ng_field_service_asst) AS ng_field_service_asst, Sum(Time_Allocation.ng_mfg_asst) AS ng_mfg_asst, Sum(Time_Allocation.ng_training) AS ng_training, Sum(Time_Allocation.ng_sys_it) AS ng_sys_it, Sum(Time_Allocation.ng_manuals) AS ng_manuals, Sum(Time_Allocation.ng_meetings) AS ng_meetings, Sum(Time_Allocation.rj_pqprocessing) AS rj_pqprocessing, Sum(Time_Allocation.rj_mqprocessing) AS rj_mqprocessing, Sum(Time_Allocation.rj_sales_asst) AS rj_sales_asst, Sum(Time_Allocation.rj_field_service_asst) AS rj_field_service_asst, Sum(Time_Allocation.rj_mfg_asst) AS rj_mfg_asst, Sum(Time_Allocation.rj_training) AS rj_training, Sum(Time_Allocation.rj_sys_it) AS rj_sys_it, Sum(Time_Allocation.rj_manuals) AS rj_manuals, Sum(Time_Allocation.rj_meetings) AS rj_meetings, Sum(Time_Allocation.conv_pqprocessing) AS conv_pqprocessing, Sum(Time_Allocation.conv_mqprocessing) AS conv_mqprocessing, Sum(Time_Allocation.conv_sales_asst) AS conv_sales_asst, Sum(Time_Allocation.conv_field_service_asst) AS conv_field_service_asst, Sum(Time_Allocation.conv_mfg_asst) AS conv_mfg_asst, Sum(Time_Allocation.conv_training) AS conv_training, Sum(Time_Allocation.conv_sys_it) AS conv_sys_it, Sum(Time_Allocation.conv_manuals) AS conv_manuals, Sum(Time_Allocation.conv_meetings) AS conv_meetings, Sum(Time_Allocation.conv_componentevalualtion) AS conv_componentevalualtion, Sum(Time_Allocation.conv_approvaldrawings) AS conv_approvaldrawings, Sum(Time_Allocation.conv_salesdrawings) AS conv_salesdrawings, Sum(Time_Allocation.conv_exportasst) AS conv_exportasst, Sum(Time_Allocation.conv_admin) AS conv_admin, Sum(Time_Allocation.conv_mfgmeetings) AS conv_mfgmeetings, Sum(Time_Allocation.conv_salesmeetings) AS conv_salesmeetings, Sum(Time_Allocation.conv_projmeetings) AS conv_projmeetings, Sum(Time_Allocation.ng_componentevalualtion) AS ng_componentevalualtion, Sum(Time_Allocation.ng_approvaldrawings) AS ng_approvaldrawings, Sum(Time_Allocation.ng_salesdrawings) AS ng_salesdrawings, Sum(Time_Allocation.ng_exportasst) AS ng_exportasst, Sum(Time_Allocation.ng_admin) AS ng_admin, Sum(Time_Allocation.ng_mfgmeetings) AS ng_mfgmeetings, Sum(Time_Allocation.ng_salesmeetings) AS ng_salesmeetings, Sum(Time_Allocation.ng_projmeetings) AS ng_projmeetings, Sum(Time_Allocation.rj_componentevalualtion) AS rj_componentevalualtion, Sum(Time_Allocation.rj_approvaldrawings) AS rj_approvaldrawings, Sum(Time_Allocation.rj_salesdrawings) AS rj_salesdrawings, Sum(Time_Allocation.rj_exportasst) AS rj_exportasst, Sum(Time_Allocation.rj_admin) AS rj_admin, Sum(Time_Allocation.rj_mfgmeetings) AS rj_mfgmeetings, Sum(Time_Allocation.rj_salesmeetings) AS rj_salesmeetings, Sum(Time_Allocation.rj_projmeetings) AS rj_projmeetings, Sum(PR_Time.PR_time) AS PR_time, max(month(Time_Allocation.TA_date)) AS TA_month, max(year(Time_Allocation.TA_date)) AS TA_year, max(Time_Allocation.EmpID) AS EmpID
FROM Time_Allocation INNER JOIN PR_Time ON Time_Allocation.TA_autonum=PR_Time.TA_autonum
WHERE ((Time_Allocation.EmpID=[Enter the Employee that you want to Query ex: 'jworker'])and((Month(Time_Allocation.TA_date))=[Enter the month that you want to Query 'm']) And ((Year(Time_Allocation.TA_date))=[Enter the year that you want to Query 'yyyy']));

If you need more info please let me know. Thx...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:23:24

group by main table field and apply aggregates fisrt. then join with this derived table from first table

jebrown55
Starting Member

3 Posts

Posted - 2009-02-11 : 11:37:56

visakh16,
I'm having a problem with the syntax of that. Would I use a GROUP BY then use a subquery for the second table?? Can you include a bit of an example. Like I said; I'm stumped...

jebrown55
Starting Member

3 Posts

Posted - 2009-02-11 : 16:14:25

I had to put a sub-query in the SELECT statement with a join and then took the original join out of the main query. I’m assuming the Access DB already recognizes the new WHERE clause from the old one, so it only asks for the information once and looks it up twice. Here is the syntax...

SELECT Sum(Time_Allocation.ng_pqprocessing) AS ng_pqprocessing, Sum(Time_Allocation.ng_mqprocessing) AS ng_mqprocessing, Sum(Time_Allocation.ng_sales_asst) AS ng_sales_asst, Sum(Time_Allocation.ng_field_service_asst) AS ng_field_service_asst, Sum(Time_Allocation.ng_mfg_asst) AS ng_mfg_asst, Sum(Time_Allocation.ng_training) AS ng_training, Sum(Time_Allocation.ng_sys_it) AS ng_sys_it, Sum(Time_Allocation.ng_manuals) AS ng_manuals, Sum(Time_Allocation.ng_meetings) AS ng_meetings, Sum(Time_Allocation.rj_pqprocessing) AS rj_pqprocessing, Sum(Time_Allocation.rj_mqprocessing) AS rj_mqprocessing, Sum(Time_Allocation.rj_sales_asst) AS rj_sales_asst, Sum(Time_Allocation.rj_field_service_asst) AS rj_field_service_asst, Sum(Time_Allocation.rj_mfg_asst) AS rj_mfg_asst, Sum(Time_Allocation.rj_training) AS rj_training, Sum(Time_Allocation.rj_sys_it) AS rj_sys_it, Sum(Time_Allocation.rj_manuals) AS rj_manuals, Sum(Time_Allocation.rj_meetings) AS rj_meetings, Sum(Time_Allocation.conv_pqprocessing) AS conv_pqprocessing, Sum(Time_Allocation.conv_mqprocessing) AS conv_mqprocessing, Sum(Time_Allocation.conv_sales_asst) AS conv_sales_asst, Sum(Time_Allocation.conv_field_service_asst) AS conv_field_service_asst, Sum(Time_Allocation.conv_mfg_asst) AS conv_mfg_asst, Sum(Time_Allocation.conv_training) AS conv_training, Sum(Time_Allocation.conv_sys_it) AS conv_sys_it, Sum(Time_Allocation.conv_manuals) AS conv_manuals, Sum(Time_Allocation.conv_meetings) AS conv_meetings, Sum(Time_Allocation.conv_componentevalualtion) AS conv_componentevalualtion, Sum(Time_Allocation.conv_approvaldrawings) AS conv_approvaldrawings, Sum(Time_Allocation.conv_salesdrawings) AS conv_salesdrawings, Sum(Time_Allocation.conv_exportasst) AS conv_exportasst, Sum(Time_Allocation.conv_admin) AS conv_admin, Sum(Time_Allocation.conv_mfgmeetings) AS conv_mfgmeetings, Sum(Time_Allocation.conv_salesmeetings) AS conv_salesmeetings, Sum(Time_Allocation.conv_projmeetings) AS conv_projmeetings, Sum(Time_Allocation.ng_componentevalualtion) AS ng_componentevalualtion, Sum(Time_Allocation.ng_approvaldrawings) AS ng_approvaldrawings, Sum(Time_Allocation.ng_salesdrawings) AS ng_salesdrawings, Sum(Time_Allocation.ng_exportasst) AS ng_exportasst, Sum(Time_Allocation.ng_admin) AS ng_admin, Sum(Time_Allocation.ng_mfgmeetings) AS ng_mfgmeetings, Sum(Time_Allocation.ng_salesmeetings) AS ng_salesmeetings, Sum(Time_Allocation.ng_projmeetings) AS ng_projmeetings, Sum(Time_Allocation.rj_componentevalualtion) AS rj_componentevalualtion, Sum(Time_Allocation.rj_approvaldrawings) AS rj_approvaldrawings, Sum(Time_Allocation.rj_salesdrawings) AS rj_salesdrawings, Sum(Time_Allocation.rj_exportasst) AS rj_exportasst, Sum(Time_Allocation.rj_admin) AS rj_admin, Sum(Time_Allocation.rj_mfgmeetings) AS rj_mfgmeetings, Sum(Time_Allocation.rj_salesmeetings) AS rj_salesmeetings, Sum(Time_Allocation.rj_projmeetings) AS rj_projmeetings, (Select Sum(PR_Time.PR_time) FROM Time_Allocation INNER JOIN PR_Time ON Time_Allocation.TA_autonum=PR_Time.TA_autonum WHERE ((Time_Allocation.EmpID=[Enter the Employee that you want to Query ex: 'jworker'])and((Month(Time_Allocation.TA_date))=[Enter the month that you want to Query 'm']) And ((Year(Time_Allocation.TA_date))=[Enter the year that you want to Query 'yyyy'])) ) AS PR_time, max(month(Time_Allocation.TA_date)) AS TA_month, max(year(Time_Allocation.TA_date)) AS TA_year, max(Time_Allocation.EmpID) AS EmpID
FROM Time_Allocation
WHERE ((Time_Allocation.EmpID=[Enter the Employee that you want to Query ex: 'jworker'])and((Month(Time_Allocation.TA_date))=[Enter the month that you want to Query 'm']) And ((Year(Time_Allocation.TA_date))=[Enter the year that you want to Query 'yyyy']));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources