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?

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`
``````
Source (Stackoverflow)