CODEWITHSUNDEEP
sql
Lazarius
Lazarius

Reputation: 53

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?

Upvotes: 2

Views: 82

Answers (1)

Blag
Blag

Reputation: 5894

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`

Upvotes: 1

Related Questions