get an average number of hours it took install a d

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get an average number of hours it took install a d

Author

Topic

sqldistinct
Starting Member

1 Post

Posted - 2012-06-24 : 12:24:52

Hi,
Hope someone can help?

I need to get an average number of hours it took install a distinct product_number and to subtotal the hours by serial number, to do this I need to sum up all of the number_hours fields for a distinct serial number for that distinct product_number, then take the sum of all those hours and divide by the number of distinct serial numbers for that distinct product id.

In the example I pasted below, the distinct product_number is 00015546279 of which there are three distinct serial numbers (67125121, 67125128, and 67225130), the sum of all of the hours for this example's product_number is 60, I would then take that number and divide it by the number of distinct serial numbers found for that distinct product_number, 00015546279, of which there are three and arrive at my average of 20 hours for the install time for that product_number. My problem is how to build an sql statement to do this and get a results table that contains this information in a results list with subtotals by serial number and average hours by product_number.

Thanks,
Pete

product_number serial_number iteml_number number_hours
00015546279 67125121 110334 8
00015546279 67125121 110334 2
00015546279 67125128 106700 2
00015546279 67125128 106700 6
00015546279 67125128 106700 6
00015546279 67225130 83489 6
00015546279 67225130 83489 8
00015546279 67225130 83489 8
00015546279 67225130 83489 8
00015546279 67225130 83489 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 13:59:50

[code]
SELECT t.product_number,TotalHrsSerial,TotalHrs * 1.0/SerialCnt AS AvgHrsPrdt
FROM
(
SELECT DISTINCT product_number,
SUM(number_hours) OVER (PARTITION BY product_number, serial_number) AS TotalHrsSerial
FROM table
)t
INNER JOIN (SELECT product_number,SUM(number_hours) AS TotalHrs,COUNT(DISTINCT serial_number) AS SerialCnt
FROM table
GROUP BY product_number
)t1
ON t1.product_number = t.product_number
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources