CODEWITHSUNDEEP
sqloracle-database
Dylaniato
Dylaniato

Reputation: 3

Group by unknown values using SQL

I have a table called "shipment" and a table called "order". The order and the shipment are related using the table "order_movement". So, in this last table, there will be the shipment_id and the order_gid. In the shipment table I have the name of the carrier (servprov_gid). What I want to do is to group all the order basing on the name of the carrier. Simple until this point. Here is my query:

select count(distinct order_release_gid) X,  servprov_gid Y
    from
        (select distinct ord.order_release_gid, ship.servprov_gid
        from order_release ord,
            shipment ship,
            order_movement om,
        where ship.shipment_gid = om.shipment_gid
        and om.order_release_gid = ord.order_release_gid
        and ship.servprov_gid in ('CNHILA.CAVL_CCWB','CNHILA.PRLG_CCPL','CNHILA.TCXS_CCWB','CNHILA.RDWY_CCWB', 'CNHILA.WAWL_CCWB'))
    group by servprov_gid

please, forget about the query form, it's not the focus of the question. So now I have all the order for a certain carrier, choosen in that list. But now I'd like to know, in the same query, all the orders by other carriers! What I'd expect is a table containing

0. X     |    Y
1. 1     |    CNHILA.CAVL_CCWB
2. ...
3. 6     |    OTHER

it's possible? Thank you

EDIT

my expected output is a "6-row" table containing the number of the orders for the 5 carrier specified in the "IN" clause and the number of all the other orders (the ones which have a different carrier)!

0. X     |    Y
1. 1     |    CNHILA.CAVL_CCWB
2. 2     |    CNHILA.PRLG_CCPL
3. 0     |    CNHILA.TCXS_CCWB
4. 2     |    CNHILA.RDWY_CCWB
5. 12    |    CNHILA.WAWL_CCWB
6. 6     |    OTHER

Upvotes: 0

Views: 156

Answers (2)

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

Skip doing the in list in the where clause, you are going to read everything anyway. Instead use a case statement to transform everyone that is not in the in list to OTHER:

select count(order_release_gid) X,  servprov_gid Y
    from
        (select distinct ord.order_release_gid,
                case
                  when ship.servprov_gid in ('CNHILA.CAVL_CCWB','CNHILA.PRLG_CCPL','CNHILA.TCXS_CCWB','CNHILA.RDWY_CCWB', 'CNHILA.WAWL_CCWB')
                  then ship.servprov_gid
                  else 'OTHER'
                end servprov_gid
        from order_release ord,
            shipment ship,
            order_movement om,
        where ship.shipment_gid = om.shipment_gid
        and om.order_release_gid = ord.order_release_gid
        )
    group by servprov_gid
    order by case servprov_gid when 'OTHER' then 2 else 1 end
           , servprov_gid

The case in the order by is only to insure that the OTHER row always is the last row.

Upvotes: 1

Tobsey
Tobsey

Reputation: 3400

You need to manually provide the same value to all of the OTHER providers so that you can group them. One way would be to use the DECODEfunction:

select 
    count(distinct order_release_gid) X,  
    ShipmentGroupID Y
from
    (select distinct 
        ord.order_release_gid, 
        decode(ship.servprov_gid,
            'CNHILA.CAVL_CCWB', 'CNHILA.CAVL_CCWB', 
            'CNHILA.PRLG_CCPL', 'CNHILA.PRLG_CCPL',
            'CNHILA.TCXS_CCWB', 'CNHILA.TCXS_CCWB',
            'CNHILA.RDWY_CCWB', 'CNHILA.RDWY_CCWB',
            'CNHILA.WAWL_CCWB', 'CNHILA.WAWL_CCWB',
            'OTHER') ShipmentGroupID
    from 
        order_release ord,
        shipment ship,
        order_movement om
    where 
        ship.shipment_gid = om.shipment_gid
        and om.order_release_gid = ord.order_release_gid
    )
group by 
        ShipmentGroupID

The decode function works like a CASE statement. The first parameter to the function is the value to be compared, then you follow with with pairs of values, the first of each pair is compared to the first parameter and if it matches then the second of the pair is returned. The extra parameter at the end is the default if no matches are found.

So if the provider is 'CNHILA.PRLG_CCPL' it will return 'CNHILA.PRLG_CCPL', but if the provider is 'CNHILA.IJustMadeThisUp' it will return 'OTHER' because none of the pairs given in the decode function matched it.

Your query though won't return a shipment method that is never used and your sample results contain a shipment provider with a count of 0.

This query can be rewritten to get those results, and you don't even need the order table:

select 
    count(distinct order_release_gid) X,  
    ShipmentGroupID Y
from
    (select distinct 
        om.order_release_gid, 
        decode(ship.servprov_gid,
            'CNHILA.CAVL_CCWB', 'CNHILA.CAVL_CCWB', 
            'CNHILA.PRLG_CCPL', 'CNHILA.PRLG_CCPL',
            'CNHILA.TCXS_CCWB', 'CNHILA.TCXS_CCWB',
            'CNHILA.RDWY_CCWB', 'CNHILA.RDWY_CCWB',
            'CNHILA.WAWL_CCWB', 'CNHILA.WAWL_CCWB',
            'OTHER') ShipmentGroupID
    from 
        shipment ship
        LEFT JOIN order_movement om ON ship.shipment_gid = om.shipment_gid
    )
group by 
        ShipmentGroupID

Upvotes: 0

Related Questions