Subquery to return one combined record instead of multiple records

Transact-SQL
1

I have this subquery below and it is returning multiple PARENT_ITEM_NOs for each ITEM_NO and it works great. The problem is I really only want it to return one record for each unique PARENT_ITEM_NO for the same LAST_RELEASE date. I want it to add the QTY_ORDERED together for both records and return just one record.

So my example Z15160-1-- is returning two records for PARENT_ITEMS record Z152165-1-- for the same date but the quantities are different. I want my query to return

Z15160-1-- 650 Z152165-1-- FAO CLOCK TOWER DISPLAY #28700 SALE_PRICE 2017-09-26 1

**SALE_PRICE - Calculated - (ORDERS.TOTAL_AMT / ORDERS.QTY_ORDERED * SUM(rpttemp16.QTYONHAND))

Capture
Capture.JPG863×400 128 KB

indent preformatted text
SELECT
rpttemp16.ITEM_NO,
ORDERS.QTY_ORDERED,
PRODUCT_STRUCTURE.PARENT_ITEM_NO,
SPEC_FILE.CUST_IDENT AS PARENT_CUST_IDENT,
(ORDERS.TOTAL_AMT / ORDERS.QTY_ORDERED * SUM(rpttemp16.QTYONHAND)) AS SALE_PRICE,
ORDERS.ORDER_DATE AS LAST_RELEASE,
Row_number()
OVER(
partition BY rpttemp16.ITEM_NO
ORDER BY ORDER_DATE, ORDERS.QTY_ORDERED ) rn
FROM rpttemp16 INNER JOIN
PRODUCT_STRUCTURE ON rpttemp16.ITEM_NO = PRODUCT_STRUCTURE.COMP_ITEM_NO INNER JOIN
SPEC_FILE ON PRODUCT_STRUCTURE.PARENT_ITEM_NO = SPEC_FILE.ITEM_NO INNER JOIN
ORDERS ON PRODUCT_STRUCTURE.PARENT_ITEM_NO = ORDERS.ITEM_NO
WHERE (rpttemp16.QTY_ORDERED IS NULL) AND (rpttemp16.SALE_PRICE IS NULL) AND (rpttemp16.LAST_RELEASE IS NULL) AND (NOT(ORDERS.COMPLETION_FLG = 'X'))
GROUP BY rpttemp16.ITEM_NO, ORDERS.QTY_ORDERED, SPEC_FILE.CUST_IDENT, PRODUCT_STRUCTURE.PARENT_ITEM_NO, ORDERS.QTY_ORDERED, ORDERS.ORDER_DATE, ORDERS.TOTAL_AMTPreformatted text

Is it possible to alter this query to do what I need it to do?

Thanks,
Stacy

2

I think you could use a CTE (common table expression) using your query above as the CTE query definition then performing a select statement on the CTE with grouping, something like

WITH CTE_Q1
AS
(your select statement)
SELECT
SUM(QTY_ORDERED)
FROM CTE_Q1
GROUP BY PARENT_ITEM_NO

I appreciate that this is really simplified but does it give you an idea and a starting point? The idea is you populate the CTE with your base query then you can select and manipulate the CTE result set as you need to using arithmetic functions and grouping.