Average of numbers in sql

Question

0.00/5 (No votes)

See more:

SQL

create table #temp2
(
salespersonid int,
orders int
)

insert into #temp2(salespersonid,orders)
select 1,24
union all select 2,24
union all select 3,48
union all select 4,72
union all select 5,24
union all select 6,18
union all select 2,48
union all select 3,24

select * from #temp2

SELECT salespersonid,sum(orders) as numbers from #temp2
group by salespersonid

--so now the output is

VB

Now i need the average without using with clause.
And also i dont want to use like count distinct id [that is 6].
Avg= sum/count.. i dont want to use this...

What i need is after

XML

SELECT salespersonid,sum(orders) as numbers from #temp2
    group by salespersonid

I need to use only AVG function , group by and sum.  

--so first we are doing group by salespersonid
--then we need average 
--

Posted 9-Aug-13 4:45am

anurag19289

Add a Solution

2 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)

Manas Bhardwaj · Answer 1 · 2013-08-09T05:47:00

Solution 1

Not sure if I understood correctly. But if I am correct you want to get the average number of sales orders made by a sales person. Correct? If so, you can do like this:

SELECT 
	salespersonid AS SalesPersonId,
	SUM(orders) AS Numbers,
	AVG(orders) AS Average 
FROM
	#temp2
GROUP BY 
        salespersonid

Posted 9-Aug-13 5:47am

Manas Bhardwaj

Comments

anurag19289 20-Aug-13 3:40am

Hi Manas-

the above query will not fetch me the exact result.

like the above query the output will be :

SalesPersonId Numbers Average
1 24 24
2 72 36
3 72 36
4 72 72
5 24 24
6 18 18

but the average i am looking for is the average of second column:
24
72
72
72
24
18

that is 47. so i need to write that query in such a way that i will get 47 as output. Now this can be done using CTE. But i wanted to write a query without cte. Please try and let me know.

anurag19289 20-Aug-13 3:59am

I got the solution..we have to use sum and average like this as below

anurag19289 · Answer 2 · 2013-08-19T21:59:00

Solution 2

--I GOT THE SOLUTION,,

SQL

SELECT AVG(NUMBERS) AS FINALAVERAGE FROM
(
  SELECT
       SUM(orders) AS Numbers
  FROM
    #temp2
  GROUP BY
    salespersonid
) T

Posted 19-Aug-13 21:59pm

anurag19289