Lazarius Lazarius - 12 days ago 6
SQL Question

SQL: Determining "Min" or "Least" of Average

I'm doing an analysis of cost per product by supplier and have been stuck for a while trying to work out the granularity or procedure of this SQL code. Table looks like something below:

Supplier ProductCount Cost
SupA 2 564.00
SupA 1 200.00
SupB 3 650.00
SupC 2 600.00
etc.


And what I want is a table that contains a summary like so:

Supplier TotalCount TotalCost AvgCost MinAvgCost (expected)
SupA 3 764.00 254.67 ?(200)
SupB 3 650.00 216.67 ?(216.67)
SupC 2 600.00 300.00 ?(300.00)


I can do the others by a simple SUM Function, but I'm confused as to how to do the MinAvgCost, since if I simply do a SELECT MIN(AvgCost) it will return to me the same value as the AvgCost.

How do I define that the code should divide Cost and ProductCount on each row, then return to me the MIN value of that formula?

Answer

This is probably what you want ?

(I've done it with MySql, you may need some change on other DB)

SELECT 
    Supplier, 
    sum(ProductCount) AS TotalCount,  
    sum(Cost) AS TotalCost,
    (sum(Cost) / sum(ProductCount)) AS AvgCost,
    min(Cost/ProductCount) AS MinAvgCost
FROM t
GROUP BY `Supplier`
Comments