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

15,942,801 members

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

1 24 2 72 3 72 4 72 5 24 6 18

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

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

Permalink

Share this answer

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.

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

--I GOT THE SOLUTION,,

SQL

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

Permalink

Share this answer

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

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900