Select query help - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select query help

Author

Topic

the_biochemist
Starting Member

1 Post

Posted - 2013-12-19 : 12:57:00

Apologies I advance I am sure this is simple but I have got myself stuck.

I have a dataset of laboratory results which in the real world are related as such

Sample_ID
-Test_Profile
-Discrete_Test - Test_Price

So there are many sample IDs each with many test profiles each with many tests. each test also has a price

the data is in a table as such:

Sample_ID, Test_Profile, Discrete_Test, Test_Price

The question I need to answer is what is the average price of each Test_Profile

I have tried various permutations of averages and group bys but always end up effectively with the average discrete test price for a test profile somehow and not the average test profile price.

The output I am looking for is effectively:

distinct Test_Profile, Average Test_Profile Price

Any advice would be much appreciated.

I know it will be something simple so please forgive my ignorance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 13:20:31

I think you need any one of the below


SELECT Test_Profile,
AVG(Test_Price * 1.0) AS AvgVal1,
SUM(Test_Price)*1.0/NULLIF(COUNT(DISTINCT Discrete_Test),0) AS AvgVal2,
SUM(Test_Price)*1.0/NULLIF(COUNT(Discrete_Test),0) AS AvgVal3
FROM Table
GROUP BY Test_Profile

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources