How can i get this result using sql server

Question

4.00/5 (1 vote)

See more:

This is my table...
select * from table1

--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------

I need this result
--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
avg 1.75 3.65 2.14(this new row)
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------

The row Avg is v1 values(1.2,2.1,1.0,2.7) is 1.75
v2 values (1.2,2.4,8.5,2.5) is 3.65
v3 values (2.0,3.4,0.56,2.6) is 2.14

the row avg is added dynamicly using avg()..

please anyone help me...

Posted 21-Dec-11 19:12pm

godbrother3

Add a Solution

3 solutions

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)

Wendelius · Accepted Answer · 2011-12-21T19:17:00

Solution 1

Perhaps something like:

SQL

select name, avg(v1),avg(v2),avg(v3)
from yourtable
group by name
with rollup

Posted 21-Dec-11 19:17pm

Wendelius

Comments

godbrother3 22-Dec-11 1:23am

thank u...

Wendelius 22-Dec-11 1:24am

You're welcome :)

godbrother3 22-Dec-11 1:40am

now i also add one more new row Median. median is half of the value..
ex. (4,3,5) - (4) is median...

thank's lot..

Wendelius 22-Dec-11 1:44am

Answered in your other question. If you want to use it with rollup, perhaps the easiest way (in sense of usage) is to create a custom aggregate.

thatraja 22-Dec-11 1:57am

5!

Wendelius 22-Dec-11 2:23am

Thank you :)

Suresh Suthar · Accepted Answer · 2011-12-21T19:21:00

Solution 3

You can use Union operator to add new row to result set.
e.g.

SQL

SELECT 'AVERAGE',AVG(V1),AVG(V2),AVG(V3) FROM TABLENAME
UNION
SELECT NAME,V1,V2,V3 FROM TABLENAME
ORDER BY 1

Posted 21-Dec-11 19:21pm

Suresh Suthar

Comments

godbrother3 22-Dec-11 1:37am

Thank's u...

Rajesh Anuhya · Accepted Answer · 2011-12-21T19:23:00

Solution 4

below Query may help you..

SQL

select name,v1 from(
select 'Avg' as name,AVG(V1) as v1 from temp
union select name,v1 from temp)  as ss

Add v2,v3 columns ...

Posted 21-Dec-11 19:23pm

Rajesh Anuhya

Comments

godbrother3 22-Dec-11 1:38am

Thanks u...