Calculate AVG & STDEV for name's values in sql query

Question

0.00/5 (No votes)

See more:

I currently have query, which calls following output1. I would like to add extra column to the query such as AVG and STDEV, which can calculate AVG and STDEV for each name's tag, such as shown in desired output2.

output1

name	Tag	        Value
AIM	00900AAC2	96.76
AIM	00900AAC2	96
AIM	00900AAC2	97
ARE	NULL	        98.5
ARE	NULL	        97

SQL

Select [Name], Tag, [Decimal price] from (
select [Name], Tag, [Decimal price], count([Decimal price]) Over(Partition by Name) cnt
from [dbo].[database_data]
where [UploadDate] = '2014-08-18'
) t
WHERE cnt>=3

Desired Output2

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	96		
AIM	00900AAC2	97		
ARE	NULL	        98.5	97.75	0.75
ARE	NULL	        97

Any help would be very much appreciated.
Many thanks

Posted 27-Aug-14 2:01am

miss786

Add a Solution

3 solutions

Solution 1

Assuming that your sql engine (you haven't told us which engine you use) has AVG and STD aggregation functions, you are a little bit out of the line with this question. You confuse an sql query with a report. A query returns a set, thus it is unordered by definition. So assuming that you get the calculation, you can end up with a result like this:

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96		
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	97		
ARE	NULL	        97
ARE	NULL	        98.5	97.75	0.75

Now let's take the other problem, if you want to stick to the "result" you posted: You can easily generate a result like this:

name	 Tag	 Value	         AVG	STD
AIM	00900AAC2	96	96.59	0.43	
AIM	00900AAC2	96.76	96.59	0.43
AIM	00900AAC2	97	96.59	0.43	
ARE	NULL	        97      97.75	0.75
ARE	NULL	        98.5	97.75	0.75

by adding subquery (not tested - and only to get the idea):

SQL

select 
[Name], 
[Tag], 
[Value], 
(select AVG([value]) from [dbo].[database_data] t2 where t2.name=t2.name) as AVG,
(select STDEV([value]) from [dbo].[database_data] t2 where t2.name=t2.name) as STD
from [dbo].[database_data] t1

But it is really hard to have the aggregations result shown only for the first row of every group - and let's say nulls elsewhere. An sql query is just not meant to do this. You really should handle this on the presentation layer, whatever that is.

Posted 27-Aug-14 2:30am

Zoltán Zörgő

Updated 27-Aug-14 2:33am

v2

Solution 2

select the columns and Use Group By

Posted 28-Aug-14 1:54am

nayeem@codeproject

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DineshMaind · Accepted Answer · 2014-09-01T10:02:00

Try following query to get rough idea to get desired output.

SQL

SELECT	t.[Name], 
		t.Tag, 
		t.[Decimal price],
		(CASE WHEN id = 1 THEN (SELECT Avg([Decimal price]) FROM [database_data] WHERE NAME = t.NAME) ELSE NULL END) [Avg],
		(CASE WHEN id = 1 THEN (SELECT STDEV([Decimal price]) FROM [database_data] WHERE NAME = t.NAME) ELSE NULL END) [STDEV]
FROM	(
			SELECT [Name], 
					Tag,
					[Decimal price], 
					row_number() Over(partition by Name order by Name) id
			FROM	[database_data]
		) t

Make changes in its conditions whatever you want.