CODEWITHSUNDEEP
sqlsql-serverdatabase
Donal McNamee
Donal McNamee

Reputation: 13

SQL query to return average of results using JOIN and GROUP BY

I have a simple manufacturing job card system that track parts and labor for an assigned job.

It consists of a JobHeader table that holds the Job Card number (JobHeader.JobNo), ID of the part being manufactured (JobHeader.RegNo) and quantity to be manufactured (JobHeader.RegNo).

There is a child table (JobLabour) that tracks all the times that have been worked on the job (JobLabour.WorkedTime)

I'm looking for a query that will return the average time taken to produce a part accross the last 5 job cards for that particular part.

The following query

SELECT TOP 5 JobHeader.RegNo, JobHeader.BOMQty, sum(JobLabour.WorkedTime) AS TotalTime FROM JobHeader INNER JOIN JobLabour ON JobHeader.JobNo=JobLabour.JobNo
WHERE JobHeader.RegNo='RM-BRU-0134' 
GROUP BY JobHeader.BOMQty, JobHeader.JobNo, JobHeader.RegNo

will return this:

Current result

But what I'm looking for is a query that will return the average BOMQty and average totalTime. Something like this:

Required result

Is there a way to do this?

Upvotes: 0

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your question explicitly mentions the "last five" but does not specify how that is determined. Presumably, you have some sort of date/time column in the data that defines this.

In SQL Server, you can use apply:

select jh.*, jl.*
from jobheader jh outer apply
     (select top (5) avg(BOMQty) as avg_BOMQty, avg(totalTime) as avg_totalTime
      from (select top (5) jl.*
            from joblabour jl
            where jl.regno = jh.regno
            order by jl.<some datetime>   -- however you determine the last five
           ) jl
      ) jl;

You can add a where clause to the outer query to filter on one or more particular jobs.

Upvotes: 1

gal peled
gal peled

Reputation: 482

If I understand you correctly this will do the work this will work for 1 RegNo='RM-BRU-0134' at a time

with topFive as (
    SELECT TOP 5 JobHeader.RegNo, JobHeader.BOMQty, sum(JobLabour.WorkedTime) AS TotalTime
    FROM JobHeader
    INNER JOIN JobLabour ON JobHeader.JobNo = JobLabour.JobNo
    WHERE JobHeader.RegNo = 'RM-BRU-0134' 
    GROUP BY JobHeader.BOMQty, JobHeader.JobNo, JobHeader.RegNo
)
select RegNo, avg(BOMQty) as BOMQty, avg(TotalTime) as TotalTime 
from topFive 
group by RegNo

Upvotes: 0

Related Questions