CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
JC97
JC97

Reputation: 1620

SQL minimum of average

For a query I need to select the records that are 'minimum of average'. So in theory I should do something like 

select min(avg(x)) from tableA;

Since nested aggregation functions are not supported I'm not sure how to achieve this. I tried subquery's as wel, but I never could make it work so anyone an idea? Pseudo code is enough, I don't need a full solution. That's why I used pseudocode myself and not giving the data I'm working on.

Thanks in advance!

EDIT: Since it's required to give example/sampledata:

I have 3 tables where I need to get the part, employee and duration from. I need the query to be grouped by part & employee and need to get the minimumaverage of duration. 

SELECT p.part, e.Name, min(avg(s.duration)) as AvgDuration
FROM store s
JOIN parts p
ON p.PartId = s.PartId
JOIN employees e
ON e.EmployeeId = s.EmployeeId
GROUP BY p.PartNr, e.Name
ORDER BY PartNr;

I know that this is a faulty query but that should make clear what I'm aiming for.

Based on the solution of Gordon,

I have following query:

SELECT m.PartNr, m.Name, min(avg_duration) as Duration
FROM (
    SELECT p.PartNr, e.Name, avg(s.Duration) as avg_duration
    FROM Store s
    JOIN parts p
    ON p.PartId = s.PartId
    JOIN employees e
    ON e.EmployeeId = s.EmployeeId
    GROUP BY p.PartNr, e.Name
) m
GROUP BY m.PartNr, m.Name;

But what this does is for each employee give the minimum avg, while only the employee with the minimum should be shown.

Upvotes: 0

Views: 8410

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can do it by using sub-query and then use min function .from discussion i got the problem and found it needs below query

select t2.PartNr,t3.Name,Prt_minDuration from 
(
select m.PartNr,min(Duration) as Prt_minDuration from
(
SELECT m.PartNr, m.Name, min(avg_duration) as Duration
FROM (
    SELECT p.PartNr, e.Name, avg(s.Duration) as avg_duration
    FROM Store s
    JOIN parts p
    ON p.PartId = s.PartId
    JOIN employees e
    ON e.EmployeeId = s.EmployeeId
    GROUP BY p.PartNr, e.Name
) m
GROUP BY m.PartNr, m.Name
) t1 group by PartNr
) t2

inner join 
(
SELECT m.PartNr, m.Name, min(avg_duration) as Duration
FROM (
    SELECT p.PartNr, e.Name, avg(s.Duration) as avg_duration
    FROM Store s
    JOIN parts p
    ON p.PartId = s.PartId
    JOIN employees e
    ON e.EmployeeId = s.EmployeeId
    GROUP BY p.PartNr, e.Name
) m
GROUP BY m.PartNr, m.Name
) t3 on t2.PartNr=t3.PartNr and t2.Prt_minDuration=t3.Duration

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The minimum of the average doesn't make sense, unless you are aggregating by something. I would expect something like:

select min(avg_x)
from (select avg(x) as avg_x
      from t
      group by ?
     ) x;

Upvotes: 4

Related Questions