I'm having trouble building a query where subitems occur. I attach below the data and the expected end result. Important thing is fact that SubitemID is not a constant. So I cannot put in my query thing like "subitemid like itemid 1". Here is my Table:

I would like to sum Values for each type of Category. So the result should be:

In other words I need to sum 1 row from each Category, because Value is constant per every ItemID in every category.

EDIT: My query is below:

   SELECT ItemID
    ,Category
    ,SUM(CASE WHEN Category = 116113 THEN ROUND(Value,4) else 0 end) as "Summary_cat1"
    ,SUM(CASE WHEN Category = 222540 THEN ROUND(Value,4) else 0 end) as "Summary_cat2"
,SUM(CASE WHEN Category in (116113,222540) THEN ROUND(Value,4) else 0 end) as "Summary_cat3"
    FROM TABLE
    GROUP BY ItemID, Category

Expected results:

CodePudding user response：

Assuming the values always be the same across records sharing the same item and category, a distinct query should work here:

SELECT DISTINCT ItemID, Category, Value
FROM yourTable;

If, on the other hand, you really want to sum the values, then you should be using:

SELECT ItemID, Category, SUM(Value) AS SumOfValue
FROM yourTable
GROUP BY ItemID, Category;

CodePudding user response：

You can use the FIRST_VALUE and LAST_VALUE analytic functions to get the two separate prices per category.

NOTE: This query will only work if there EXACTLY 2 different values per item.

WITH
    items (ItemID,
           SubitemID,
           Category,
           VALUE)
    AS
        (SELECT 'X', 'X1', '116113', 115 FROM DUAL
         UNION ALL
         SELECT 'X', 'X2', '116113', 115 FROM DUAL
         UNION ALL
         SELECT 'X', 'X1', '222540', 100 FROM DUAL
         UNION ALL
         SELECT 'X', 'X2', '222540', 100 FROM DUAL
         UNION ALL
         SELECT 'Y', 'Y1', '116113', 204.58 FROM DUAL
         UNION ALL
         SELECT 'Y', 'Y2', '116113', 204.58 FROM DUAL
         UNION ALL
         SELECT 'Y', 'Y4', '222540', 500 FROM DUAL
         UNION ALL
         SELECT 'Y', 'Y5', '222540', 500 FROM DUAL)
  SELECT DISTINCT
         itemid,
         category,
         FIRST_VALUE (VALUE) OVER (PARTITION BY itemid)     AS summary_cat1,
         LAST_VALUE (VALUE) OVER (PARTITION BY itemid)      AS summary_cat2,
           FIRST_VALUE (VALUE) OVER (PARTITION BY itemid)
           LAST_VALUE (VALUE) OVER (PARTITION BY itemid)    AS summary_cat3
    FROM items i
ORDER BY itemid, category;


ITEMID    CATEGORY       SUMMARY_CAT1    SUMMARY_CAT2    SUMMARY_CAT3
_________ ___________ _______________ _______________ _______________
X         116113                  115             100             215
X         222540                  115             100             215
Y         116113               204.58             500          704.58
Y         222540               204.58             500          704.58