SQL commands to get data using sub query

Question

I am trying to get some data like PRODUCT_ID, PRODUCT_TITLE and STOCK by using joins from 3 tables. The 3 table looks like this

-----------
| METERS  |
-----------
| METER_ID|
| PARK_ID |
| ZONE_ID |
-----------

----------------------
|  MT_PRODUCT_DETAILS |
----------------------
|  PRODUCT_ID         |
|  PRODUCT_TITLE      |
|  DESCRIPTION        |
-----------------------         

-----------------------
| MT_AS_EVENT_CURRENT  |
-----------------------
| DEVICE_ID             |
| MORE_DATA             |
| PRODUCT_ID            |
| STOCK_PRODUCT         |
| STATUS                |
| DATE_DEVICE           |
| EVENT_TYPE_ID         |
-------------------------

I want to get the stock count of all the products by using this query.

SELECT 
    PRODUCT_TITLE, SUM(REMAINING_STOCK) STOCK 
FROM
    (SELECT 
         PRODUCT_TITLE, REMAINING_STOCK 
     FROM 
         (SELECT 
              PRODUCT_ID, SUM(REMAINING_STOK) REMAINING_STOCK 
          FROM 
              (SELECT 
                   METER_ID, PRODUCT_ID, MAX(REMAINING_STOCK) keep
            (DENSE_RANK last ORDER BY DATE_DEVICE) REMAINING_STOCK  
               FROM 
                   (SELECT 
                        METERS.METER_ID, DATE_DEVICE, PRODUCT_ID, STOCK_PRODUCT REMAINING_STOCK 
                    FROM 
                        MT_AS_EVENT_CURRENT EV 
                    INNER JOIN
                        METERS ON EV.DEVICE_ID = METERS.METER_ID
                    WHERE 
                        EVENT_TYPE_ID = 1111
                        AND EV.DATE_DEVICE BETWEEN SYSDATE - 60 AND SYSDATE)
               GROUP BY
                   METER_ID, PRODUCT_ID)
        GROUP BY
            PRODUCT_ID) STOCKS 
    INNER JOIN
        MT_PRODUCT_DETAILS PRODUCT ON STOCKS.PRODUCT_ID = PRODUCT.PRODUCT_ID) 
GROUP BY 
    PRODUCT_TITLE

By using this query I can get the data of STOCK count and PRODUCT_TITLE, but not able to get the PRODUCT_ID.

So can someone please let me know how can I get the PRODUCT_ID by making some minor changes in the query?

FYI, I tried to use PRODUCT_ID in the outer query itself. But somehow that is not working.

The output should be PRODUCT_ID, PRODUCT_TITLE, STOCK (total count) GROUP BY PRODUCT_TITLE

Answer 1

Tried simplifying your query and added the PRODUCT_TITLE in the inner query itself which should produce your desired output as follows:

SELECT
    T.PRODUCT_ID,
    T.PRODUCT_TITLE,
    SUM(REMAINING_STOK) REMAINING_STOCK 
  FROM
    (
        SELECT
            METERS.METER_ID,
            EV.PRODUCT_ID,
            MAX(EV.STOCK_PRODUCT) KEEP(DENSE_RANK LAST ORDER BY EV.DATE_DEVICE) REMAINING_STOCK,
            PRODUCT.PRODUCT_TITLE
          FROM
                 MT_AS_EVENT_CURRENT EV
             INNER JOIN METERS ON EV.DEVICE_ID = METERS.METER_ID
             INNER JOIN MT_PRODUCT_DETAILS PRODUCT ON EV.PRODUCT_ID = PRODUCT.PRODUCT_ID
         WHERE
                EV.EVENT_TYPE_ID = 1111
               AND EV.DATE_DEVICE BETWEEN SYSDATE - 60 AND SYSDATE
         GROUP BY
            METERS.METER_ID,
            EV.PRODUCT_ID,
            PRODUCT.PRODUCT_TITLE
    ) T
 GROUP BY
    T.PRODUCT_ID,
    T.PRODUCT_TITLE;