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

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

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

## Solution 2

--I GOT THE SOLUTION,,

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