srikanth suryadevara srikanth suryadevara - 1 month ago 5
SQL Question

SELECT query using group by

I don't know why the following query is not working:

SELECT whs_code, pdt_code,case_dt_yyyymmdd, fresh_frozen_status,
SUM(qty_cases_on_hand)-Qty, SUM(qty_weight_on_hand)-Wt, operation
FROM
(
SELECT whs_code,pdt_code,case_dt_yyyymmdd,fresh_frozen_status,operation,SUM(qty_cases_on_hand) AS Qty, SUM(qty_weight_on_hand) AS Wt
FROM tbl_inventory_activity_rpt1
WHERE operation ='RU'
GROUP BY whs_code,pdt_code,case_dt_yyyymmdd,fresh_frozen_status,operation
)
WHERE operation='SU'
GROUP BY whs_code,pdt_code,case_dt_yyyymmdd,fresh_frozen_status,operation`


The error is :


Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'WHERE'.




To make it simple to understand what i am trying to do here, please see the example

I need data as result of


(SELECT x FROM tbl_table Where column y='SU')-(SELECT x FROM tbl_table Where column y='RU')

Answer
    SELECT ru.whs_code,
           ru.pdt_code,
           ru.case_dt_yyyymmdd,
           ru.fresh_frozen_status,
           ru.operation,
           ru.Qty - su.Qty AS Qty_Diff,
           ru.Wt - su.Wt AS Wt_Diff
      FROM    
    (
        SELECT whs_code,
               pdt_code,
               case_dt_yyyymmdd,
               fresh_frozen_status,
               operation,
               SUM(qty_cases_on_hand) AS Qty, 
               SUM(qty_weight_on_hand) AS Wt
          FROM tbl_inventory_activity_rpt1 
         WHERE operation ='RU'
       GROUP BY whs_code,pdt_code,case_dt_yyyymmdd,fresh_frozen_status,operation 
    ) ru,
    (
        SELECT whs_code,
               pdt_code,
               case_dt_yyyymmdd,
               fresh_frozen_status,
               operation,
               SUM(qty_cases_on_hand) AS Qty, 
               SUM(qty_weight_on_hand) AS Wt
          FROM tbl_inventory_activity_rpt1 
         WHERE operation ='SU'
       GROUP BY whs_code,pdt_code,case_dt_yyyymmdd,fresh_frozen_status,operation 
    ) su
WHERE ru.whs_code = su.whs_code
  AND ru.pdt_code = su.pdt_code
  AND ru.case_dt_yyyymmdd = su.case_dt_yyyymmdd
  AND ru.fresh_frozen_status = su.fresh_frozen_status
  AND ru.operation = su.operation;
Comments