SQL Condition with SUM and MAX-CodePudding

The context I have B2C orders, and an order can contain X items, in parallel I have different container with max sizes. The purpose of my query is to count the total volume of items for an order and compare with the max volume of each container. My problem is that I can’t make a query or I compare a SUM(my_colonn) > max_cntr_vol.

Do you have any idea?

select o.tc_order_id, sum(ic.unit_volume), ct.cntr_type as type, ct.cntr_size as type_pochette, ct.max_cntr_vol as vol_max_pochette
from cntr_type ct
    inner join orders o on ct.cntr_type = o.ref_field_6
    inner join order_line_item oli on o.order_id = oli.order_id
    inner join item_cbo ic on oli.item_id = ic.item_id
    inner join (select max(max_cntr_vol) as vol, max(cntr_type) as cntr_type, max(cntr_size) as cntr_size from cntr_type where 1=1 group by cntr_type)
    t on ct.cntr_type = t.cntr_type
where sum(ic.unit_volume) > t.vol and ct.cntr_size = t.cntr_size and o.ref_field_7 = '10203040' group by o.tc_order_id, ct.cntr_type, ct.cntr_size, ct.max_cntr_vol;

CodePudding user response：

I don't fully understand your question, but I would look at using CTEs (Common Table Expressions).

Your cntr_type table and query in particular looks funny, you should avoid using column names that are the same as the table name. I'm not sure what the key for this table is, but I assume it is ctr_type. Then using max(ctr_type) is odd when you're grouping by that column, and triply odd if that is the primary key.

You can use CTEs to break this up into two queries, and look at each individually to see if they have the right data you can use for linking:

with ContainerType as (
  select ctr_type,
         max_cntr_vol,
         cntr_size
  from   ctr_type
), OrderTotals as (
  select o.tc_order_id,
         o.ref_field_6 as ctr_type,
         o.ref_field_7
         sum(ic.unit_volume) as OrderTotalUnits,
   from  orders
   inner join order_line_item oli on o.order_id = oli.order_id
   inner join item_cbo ic on oli.item_id = ic.item_id
   group by o.tc_order_id, o.ref_field_6, o.ref_field_7
)
select o.tc_order_id,
       o.OrderTotalUnits,
       o.ctr_type,
       ct.cntr_size as type_pochette,
       ct.max_cntr_vol as vol_max_pochette
from   OrderTotals ot
join   ContainerType ct on ct.ctr_type = ot.ctr_type
where  o.ref_field_7 = '10203040'

CodePudding user response：

I would start with the total volume of the order line items regardless of a final container type that the items would ultimately be boxed into. Then join to the rest. But in this case, you are looking for a specific SINGLE order (hope you have an index on ( tc_order_id, ref_field_7, ref_field_6 ).

However, you are also grouping per container type based on the item information. So one order COULD have multiple container types and you could see 1 order with 3 container types included.

Now, your JOIN condition is a little confusing. If you are comparing to a container type on the SUM of order volume being GREATER than the size of the container baffles me. I would think you would want to know what CONTAINERS have a size big enough to put all the ordered line items within it. That could just be an oops on equality which I have changed.

Since the query of order has the container type (ref_field_6), and line items have the volume, I did not see any QUANTITY, such as I ordered 5 widgets, each with a volume of 3 requiring a total of 15. You may need to adjust the SUM() to include a quantity check as well.

Once that is summed and pre-aggregated by type, you can join to the type table without having to requery it by max. An ID will only have one ID in the given type table, wont it? Or, maybe not. If the type is a BOX, and you have a 5x10 box vs a 12x18 box, by whatever other dimensions. Which is why you have a max volume of a given container type being considered, and hence my comment that the box volume is MORE that the need to ship the items. I would then order based on the lowest volume for said given box size to complete the order so you dont try to fill a 3x5 item into a 12x18 box when a 5x10 would work.

select
        OSum.tc_order_id,
        OSum.Cntr_Type,
        OSum.CntrVolume,
        ct.Cntr_Size,
        ct.max_cntr_vol
    from
        ( select 
                o.tc_order_id, 
                o.ref_field_6 Cntr_Type,
                sum(ic.unit_volume) CntrVolume 
            from 
                orders o 
                    inner join order_line_item oli 
                        on o.order_id = oli.order_id
                        inner join item_cbo ic 
                            on oli.item_id = ic.item_id
            where
                o.ref_field_7 = '10203040'
            group by
                o.tc_order_id, 
                o.ref_field_6 ) OSum
            JOIN Cntr_Type ct
                on OSum.Cntr_Type = ct.Cntr_Type
                AND OSum.CntrVolume > ct.max_cntr_vol

Anyhow, as I digress on my thoughts of what you are encountering, let me know how close and if we need to adjust this to better fit your needs, not just trying to write a query.