Hi Team (Again).. further to an original post I am trying to get 2 further fields added to my Query / Report. BoxCount & AverageWeight
Here is the existing query.
SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate
FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight
GROUP BY pickerid,currentdate,pickerhours,pickername) AS [%$##@_Alias]
WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1))
GROUP BY pickerid, pickername;
This gives me a summary by Pickerid, Pickername, Pickerstotals, Hourly rate... All is good with this..
I now what to add a count of boxes (summed) for each picker & average box weight..
The Table is structured 1 entry for every box weighed. A pickerid, Weight of the box, date, time etc is also added..
My Output would be as follows:-
Pickerid Pickername Pickerstotal hourlyrate Boxcount AverageWeight
1 Mark 200.00 12 50 4.5
2 Steve 150.00 13 37 3.75
I have tried adding a count(*) in both select statements but it will always return "1" as all the records for that picker are tallied into 1 output record. I need the count of records for that picker before it is tallied..
The averagweight may be a hard one... it needs to do a avg(currentweight) for each picker.
Make sense.. I hope so and I do appreciate your help... Really