Total columns in Row

Question

MY SQL looks right but I am not getting any output for TotalRecvd (always zero). Any reason why I am not getting totals?

SELECT tblfanuctrainedparts.partnum,
       tblfanuctrainedparts.cycletime,
       tblfanuctrainedparts.partname,
       SUM(schain_cca_26_week_plan_recv.wk_1) AS SumOfWK_1,
       SUM(schain_cca_26_week_plan_recv.wk_2) AS SumOfWK_2,
       SUM(schain_cca_26_week_plan_recv.wk_3) AS SumOfWK_3,
       SUM(schain_cca_26_week_plan_recv.wk_4) AS SumOfWK_4,
       SUM(schain_cca_26_week_plan_recv.wk_5) AS SumOfWK_5,
       SUM(schain_cca_26_week_plan_recv.wk_6) AS SumOfWK_6,
       SUM(schain_cca_26_week_plan_recv.wk_7) AS SumOfWK_7,
      SUM( schain_cca_26_week_plan_recv.wk_8) as SumOfWK_8,
      (SumOfWK_1 +SumOfWK_2 ) AS TotalRecvd
FROM   schain_cca_26_week_plan_recv
       INNER JOIN tblfanuctrainedparts
               ON schain_cca_26_week_plan_recv.part_nbr =
                  tblfanuctrainedparts.partnum

GROUP  BY tblfanuctrainedparts.partnum,
          tblfanuctrainedparts.cycletime,
          tblfanuctrainedparts.partname

Answer 1

As jarlh mentioned you can't reference alias in MS SQL or MS Access in the same select. You would have to either wrap an outer query or sum the aggregates.

Try this and see if it works.

SELECT tblfanuctrainedparts.partnum,
       tblfanuctrainedparts.cycletime,
       tblfanuctrainedparts.partname,
       SUM(schain_cca_26_week_plan_recv.wk_1) AS SumOfWK_1,
       SUM(schain_cca_26_week_plan_recv.wk_2) AS SumOfWK_2,
       SUM(schain_cca_26_week_plan_recv.wk_3) AS SumOfWK_3,
       SUM(schain_cca_26_week_plan_recv.wk_4) AS SumOfWK_4,
       SUM(schain_cca_26_week_plan_recv.wk_5) AS SumOfWK_5,
       SUM(schain_cca_26_week_plan_recv.wk_6) AS SumOfWK_6,
       SUM(schain_cca_26_week_plan_recv.wk_7) AS SumOfWK_7,
       SUM(schain_cca_26_week_plan_recv.wk_8) as SumOfWK_8,
       SUM(schain_cca_26_week_plan_recv.wk_1) + SUM(schain_cca_26_week_plan_recv.wk_2) + SUM(schain_cca_26_week_plan_recv.wk_3) + 
       SUM(schain_cca_26_week_plan_recv.wk_4) + SUM(schain_cca_26_week_plan_recv.wk_5) + SUM(schain_cca_26_week_plan_recv.wk_6) +
       SUM(schain_cca_26_week_plan_recv.wk_7) + SUM(schain_cca_26_week_plan_recv.wk_8) AS TotalRecvd
FROM   schain_cca_26_week_plan_recv
       INNER JOIN tblfanuctrainedparts
               ON schain_cca_26_week_plan_recv.part_nbr =
                  tblfanuctrainedparts.partnum

GROUP  BY tblfanuctrainedparts.partnum,
          tblfanuctrainedparts.cycletime,
          tblfanuctrainedparts.partname