CODEWITHSUNDEEP
sqloracle-databasefunctioncountconditional-statements
Kacper Werema
Kacper Werema

Reputation: 448

Oracle - Count condition case

I am facing a problem with a query. My goal is to get all product names, unit of mass, quantity and number of pallets they are located in. The problem is with number of pallets of item.

QUERY:

SELECT "Product_Name"
       , "Unit_of_Mass"
       , SUM("Quantity_Per_UOM")
       , Count(*) as "Number_Of_Pallet" 
FROM
    (select p.prod_desc as "Product_Name"
           , s.quantity as "Quantity_Per_UOM"
            , u.description as "Unit_of_Mass"
            , s.container_id
            , s.product_id
    from wms_stock s
    join wms_product p on p.product_id = s.product_id
    join wms_uom u on p.uom_base_id = u.uom_id
    )
group by "Product_Name", "Unit_of_Mass"

It almost works. The problem is I need to do some condition in Count(*) (that's what I think should be done). In table wms_stock I got product_id and container_id, and when in some row they are same it should count the number of pallets as 1 but still add the quantities.

So from first select:

 Product_Name | Quantity | UnitOfMass | ContainerId | ProductId
 A            |        2 | kg         |          10 |        11
 A            |        1 | kg         |          10 |        11
 B            |        2 | kg         |          11 |        12

I should get result

Product_Name | Quantity_Per_UOM | UnitOfMass | Number_Of_Pallet
A            |                3 | kg         |                1
B            |                2 | kg         |                1

Upvotes: 0

Views: 56

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try below condition in your select list - 

COUNT(DISTINCT ContainerId || ProductId)

Just for your information, || is not an operator rather it is concatenation operator in Oracle. So i have just concat both the columns and picked up the distinct from them.

Upvotes: 1

Related Questions