How to use grouping by XXX, and using aggregate funtion correctly? WITH MIN VALUES

Question

I am trying to fetch records from 4 tables by using joins. These tables are rates, carriers, depots and margin. I am able to get the records that needed from this 4 table, but when I am using a group by and aggregate function (MIN), my min sell column is correct, however, the other data such as carrierID, depotID and ratesID are different.

I have OriginType (OT) and DestinatioType (DT)and there are 2 cases for each; Depot and Door. So when I group them, I get four options as (services):

• Depot To Depot
• Depot To Door
• Door To Depot
• Door To Door

I am trying to fetch the min(rates) as 'Sell' for each of these service and display it. The Sell is calculated as:

1. autoRate table as A has a column Buy, carrier, OT, DT, Origin (Value: Canberra), Destination (Value: Melbourne) and Car (Value :4WD/Van)
2. Left Join with carriers as C with A.carrier = C.ID
3. C.Fuellevy column as Percentage ((C.FuelLevy * A.buy) + C.FuelLevy) as EQ1
4. ((EQ1 * 10%) + EQ1) as EQ2
5. EQ2 price for each row with have a Margin table Percentage. For example, if the EQ2 value is 400, then It will look in the Margin table, find the range (low and high as 350(low) and 500(high)) and its percentage is 25%, so ((EQ2 * 25%)+ EQ2) gives the sell value.

I am not too sure how to upload my data and database table here, so I tried to explain here what I want.

the query I build is:-

Select   Depo.*,  DL.id as DepoID,  DL.carrier as CarNo, DL.depotCity, DL.depoSuburb, min(Depo.Sell)  as sellcost , Depo.OriginType as OT From (
    Select Mar.*,  M.MarginPer, round((eq2 * M.MarginPer) + eq2)as Sell   From (
                        Select GST.* , EQ1 as 'FinalEQ1' , round((EQ1 * .10) + EQ1,2) as eq2 From (
                                Select A.ID as RateID, A.Origin, A.OriginState, A.Destination, A.DestinationState, A.Carrier as RateCarrier, A.Car as CarType, A.Buy as Buy, A.OriginType, A.DestinationType ,
                                C.ID as CarrierID, C.Carrier, C.FuelLevy , round((A.buy * C.FuelLevy) + A.Buy, 2) As EQ1,
                                CONCAT(A.OriginType, ' to ' ,A.DestinationType ) as service,
                                 D.id as DepoID,  D.carrier as CarNo, D.depotCity, D.depoSuburb
                                from carrier C
                                left join autorates A on A.carrier = C.ID
                                left join dList D on D.carrier =  C.ID
                                where A.origin = 'Canberra' and A.destination = 'Melbourne' and A.car = '4WD/Van'  AND  D.carrier = A.carrier AND A.goodsAllowed =  0
                                AND C.Disabled = 0
                                AND D.depotCity  = 'Canberra'
                                 order by  EQ1
                            ) As GST
                        order by  eq2
             ) As Mar
             Left Join margin M on Mar.eq2 >=  M.low and  Mar.eq2 <= M.high
                   order by  Sell
        ) As Depo
            Left  Join dList DL  on DL.Carrier = Depo.RateCarrier
            Where DL.depotCity = 'Melbourne'
             group by OT
             order by  sellcost

Results before the Group by and MIN():-

As we see the sellcost value 412 and the carNo is 51 here

And in this screenshot, the carrierno has changed, but the the MIN value remains the same.

Answer 1

NOTE: Not a final answer, but a query to test and see if this part works. If so, I'll provide the rest of the query.

---

If you break the problem down, I think what you have to do is to identify the lowest Sell for each combination of OriginType and DestinationType. Then join to the tables to get the matching rows.

The query below doesn't get all of the detail info, but it should get the rows with the lowest Sell value. If it works, then I can add to the query to get the other, matching data.

One aspect that question is the use of LEFT JOIN. That might include rows that do not match up with the ON clauses, essentially adding columns with null values. This might impact performance, and using just a JOIN might work better. If you test this and it works as stated below, run it again without the LEFT that is in front of each of the JOIN clauses. If you get the same results, let me know in the comments.

Give it a try and see if you get the four values for each of the combinations of OriginType and DestinationType. If it does, I'll update the answer with the rest of the query.

SELECT
    A.OriginType,
    A.DestinationType,
    round(A.buy * (1.0 + C.FuelLevy), 2) AS EQ1,
    round(round(A.buy * (1.0 + C.FuelLevy), 2) * 1.10, 2) AS eq2,
    MIN(round((eq2 * (1.0 + M.MarginPer)))) AS Sell
FROM carrier C
LEFT JOIN autorates A
    ON A.carrier = C.ID
LEFT JOIN dList D
    ON D.carrier = A.carrier
LEFT JOIN margin M
    ON round(round(A.buy * (1.0 + C.FuelLevy), 2) * 1.10, 2) BETWEEN M.low AND M.high
WHERE A.origin = 'Canberra'
    AND A.destination = 'Melbourne'
    AND A.car = '4WD/Van'
    AND A.goodsAllowed =  0
    AND C.Disabled = 0
    AND D.depotCity  = 'Canberra'
GROUP BY LOWER(A.OriginType), LOWER(A.DestinationType)
ORDER BY Sell

Answer 2

I Just managed the get query. This will Work as I have tested on my SQL-WB

SELECT DepoD.*, DL2.id, DL2.carrier AS a, DL2.depotCity
    , DL2.depoSuburb AS 'PickupSub', MIN(Sell) AS sellcost 
FROM ( 
    SELECT Depo.*, DL.id AS DepoID, DL.carrier AS CarNo, DL.depotCity, DL.depoSuburb AS 'PickupSub'
        , DL.depoSuburb AS 'DestSub', MIN(Sell) AS sellcost, Depo.OriginType AS OT 
    FROM (
        SELECT Mar.*, M.MarginPer, MIN(round((eq2 * M.MarginPer) + eq2)) AS Sell 
        FROM (
            SELECT GST.*, EQ1 AS 'FinalEQ1', MIN(round((EQ1 * .10) + EQ1, 2)) AS eq2 
            FROM (
                SELECT A.ID AS RateID, A.Origin, A.OriginState, A.Destination, A.DestinationState
                    , A.Carrier AS RateCarrier, A.Car AS CarType, A.Buy AS Buy,  A.OriginType
                    , A.DestinationType, C.ID AS CarrierID, C.Carrier, C.FuelLevy
                    , round((A.buy * C.FuelLevy) + A.Buy, 2) AS EQ1
                    , CONCAT(A.OriginType, ' to ', A.DestinationType ) AS service 
                FROM carrier C
                LEFT JOIN autorates A ON A.carrier = C.ID
                WHERE A.origin = 'Melbourne' 
                    AND A.destination = 'Canberra' 
                    AND A.car = '4WD/Van' 
                    AND A.goodsAllowed = 0
                    AND C.Disabled = 0
                ORDER BY EQ1 ASC
            ) AS GST
            GROUP BY service, CarrierID
            ORDER BY eq2
        ) AS Mar
        LEFT JOIN margin M ON Mar.eq2 >= M.low AND Mar.eq2 <= M.high
        GROUP BY service
        ORDER BY Sell
    ) AS Depo
    LEFT JOIN dList AS DL ON DL.Carrier = Depo.RateCarrier
    WHERE DL.depotCity IN('Melbourne', 'Canberra') 
    GROUP BY carrier, service
    ORDER BY sellcost
) AS DepoD
LEFT JOIN dList DL2 ON DL2.Carrier = DepoD.RateCarrier
WHERE DL2.depotCity IN('Melbourne', 'Canberra') 
GROUP BY carrier, service
ORDER BY sellcost