A.K. Singh A.K. Singh - 4 years ago 141
SQL Question

Oracle Subquery issue,finding difference between previous year and current year sold product quantity

I am new to Oracle database but i have a good experience on Teradata and MSSQL
while learning oracle i am facing one conceptual confusion here following two tables:

Create table PRODUCTS
(
PRODUCT_ID INTEGER NOT NULL PRIMARY KEY,
PRODUCT_NAME VARCHAR2(30)
);
CREATE TABLE SALES
(
SALE_ID INTEGER NOT NULL PRIMARY KEY,
PRODUCT_ID INTEGER,
YEAR INTEGER,
QUANTITY INTEGER,
PRICE INTEGER,
FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);


Sample values to the table are:

INSERT INTO PRODUCTS VALUES ( 100, 'A');
INSERT INTO PRODUCTS VALUES ( 200, 'B');
INSERT INTO PRODUCTS VALUES ( 300, 'C');
INSERT INTO PRODUCTS VALUES ( 400, 'D');
INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 5000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 5000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 8, 5000);
INSERT INTO SALES VALUES ( 4, 200, 2010, 10, 9000);
INSERT INTO SALES VALUES ( 5, 200, 2011, 15, 9000);
INSERT INTO SALES VALUES ( 6, 200, 2012, 20, 9000);
INSERT INTO SALES VALUES ( 7, 300, 2010, 20, 7000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 7000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 7000);


My Objective is to find the following from this table:
****Edited****
My expected result is :
Explanation
For product suppose 'A' Quantity Difference=(Current_Year_Quantity - Previous_Year Quantity) AND IF quantity difference is greater than 0 then it should display the product name only.
In above case:
The output will be
/** We only need to compare the previous year quantity sold with current year quantity sold if the current year quantity is greater than previous year we need to display that product name**/


Product_Name
--------------------
B
C


1-Difference between the quantities of a product with its previous year’s quantity?

and for this purpose i am using the following query:

SELECT
PRODUCT_NAME
from
(
SELECT
P.PRODUCT_NAME,
P.PRODUCT_ID,
S.PRODUCT_ID,
((S.QUANTITY)-(LEAD(S.QUANTITY,1,0) OVER (PARTITION BY P.PRODUCT_ID ORDER BY S.YEAR DESC))) AS QUANTITY_DIFFERENCE
FROM
PRODUCTS P,
SALES S
WHERE
QUANTITY_DIFFERENCE>=0
)A
GROUP BY PRODUCT_NAME
;


It is giving the following error, I tried to fix it but unable to achieve the correction.

ORA-00904: "QUANTITY_DIFFERENCE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:


EDITED
I had just corrected the previous error by suggestion of folk, my modified query is now:

SELECT
PRODUCT_NAME,
QUANTITY_DIFFERENCE
from
(
SELECT PRODUCT_NAME,
QUANTITY_DIFFERENCE FROM (
SELECT
P.PRODUCT_NAME,
P.PRODUCT_ID,
S.PRODUCT_ID,
((S.QUANTITY)-(LEAD(S.QUANTITY,1,0) OVER (PARTITION BY P.PRODUCT_ID ORDER BY S.YEAR DESC))) AS QUANTITY_DIFFERENCE
FROM
PRODUCTS P,
SALES S
) WHERE
QUANTITY_DIFFERENCE>=0
)A
GROUP BY PRODUCT_NAME,
HAVING QUANTITY_DIFFERENCE>=0
;


But now i am receiving following error:

ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:

Please also guide me for the correct logical approach for achieving the objective of "Finding the difference between current year quantity and previous years' quantity. For example suppose the 2011 quantity was 10 and 2012 quantity was 8 then it should -2 but currently i need to show only quantities difference which is having difference more than zero.


Could you folks guide me on how to fix this issue, is there any other approach to write the same query?

Answer Source

No group by is necessary. But you do have to specify what your current year is. Otherwise, your query will start considering multiple year sets of data, and that doesn't sound like what you want.

select p.product_name
  from (select s.*,
               s.quantity - lag(s.quantity) over (partition by product_id order by year) as qty_diff
          from sales s) s
  join products p
    on p.product_id = s.product_id
 where s.year = 2012 -- set your current year here
   and s.qty_diff > 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download