SQL SUM OVER PARTITION BY 2 columns not working

Common question, I know. Just haven’t been able to find a solution to my question, so hit me with the removal and or downvotes if you must.

(Oracle 12c)

I have data that looks like this:

Date        ITEM   QTY

01-MAR-20   STS    6920
01-MAR-20   STS    2581
01-MAR-20   S01    22606
01-MAR-20   S01    22312
01-MAR-20   S01    56000
....

I want to get QTY to aggregate (sum) at the Date and item level with only one record for each unique item on each date, so it looks like this:

Date        ITEM   QTY
01-MAR-20   STS    9501
01-MAR-20   S01    100918

The query I’m trying to use to do this is:

SELECT 

WO.DATE,
D.ITEM,
SUM(WO.QUANTITY) OVER (PARTITION BY WO.DATE, D.ITEM) AS QTY

FROM SCHEMA_1.WO,
SCHEMA_2.D

WHERE WO.ITEM_DIM_KEY = D.ITEM_DIM_KEY AND
(DATE > '01 MAR 2020' AND DATE < '01 JUL 2020')
ORDER BY WO.COMPLETED_DATE;

Answer

A date data type in Oracle can have a time component. So you need to be careful. Unless you know hat you have no time component, trunc() is safer. Also, you can use the date keyword to handle date constants: SELECT

SELECT TRUNC(WO.DATE), D.ITEM,
       SUM(WO.QUANTITY)
FROM SCHEMA_1.WO JOIN
     SCHEMA_2.D
     ON WO.ITEM_DIM_KEY = D.ITEM_DIM_KEY 
WHERE WO.DATE >= DATE '2020-03-01' AND
      WO.DATE < DATE '2020-07-01'
GROUP BY TRUNC(WO.DATE), D.ITEM
ORDER BY TRUNC(WO.DATE);

Notes:

• You don’t need an analytic function. Aggregation should be sufficient.
• Use proper, explicit, standard, readable JOIN syntax.
• I assume the column in the ORDER BY is intended to be the first column in the result set.