Click here to Skip to main content
15,942,801 members
Please Sign up or sign in to vote.
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
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

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