CODEWITHSUNDEEP
sql
Tushar
Tushar

Reputation: 108

How to find average without using group by statement in sql

I want to know if its possible to get the functionality of group by with using it. I need to find average without using group by. basically I am looking for an alternative for the below simple query without using group by.

SELECT 
  AVG(salary)
  , g.emp_id 
FROM #emp e ,#salary d 
WHERE e.emp_id=d.emp_id 
GROUP BY e.emp_id

Upvotes: 2

Views: 17154

Answers (5)

Vijay Sharma
Vijay Sharma

Reputation: 831

Try This:

select * from (SELECT AVG(salary) , g.emp_id FROM #emp e ,#salary d WHERE e.emp_id=d.emp_id GROUP BY e.emp_id) a`

Upvotes: 0

David Hammond
David Hammond

Reputation: 3306

One option

SELECT  e.emp_id ,
    ( SELECT    AVG(salary)
      FROM      #salary d
      WHERE     d.emp_id = e.emp_id )
FROM    #emp e

Upvotes: 4

Johan
Johan

Reputation: 76537

If your DB supports partition you can do:

SELECT e.emp_id
       , AVG(s.salary) OVER(PARTITION BY s.emp_id) AS average_salary
FROM #emp e
INNER JOIN #salary s ON (e.emp_id = s.emp_id)

I fail to see the purpose of this exercise however.
It just makes your query harder to read, harder to debug and replaces commonly used syntax with obscure code.

Group by rocks
Did you know that group by even as a 'grant total' function build in

SELECT 
  AVG(d.salary)
  , e.emp_id 
FROM #emp e 
INNER JOIN #salary s ON (e.emp_id = s.emp_id) 
GROUP BY e.emp_id WITH ROLLUP

See: SQL-server: http://msdn.microsoft.com/en-us/library/bb522495.aspx
MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Remark
Do not use implicit SQL '89 syntax, as much as I love Bill Joel's we didn't start the fire it is time to move on to the much better SQL '92's Explicit join syntax.
As for songs, 1989 really has a better year:

Upvotes: 3

user330315
user330315

Reputation:

Are you looking for something like this?

SELECT AVG(d.salary) over (partition by null),
       e.emp_id 
FROM #emp e
  JOIN #salary d ON e.emp_id = d.emp_id;

It will return all employee ids and the average salary calculated for all employees (so it will contain the same information for all rows).

Upvotes: 0

dfb
dfb

Reputation: 13289

If you really wanted to do this, you could SELECT ... ORDER BY employee and then write a cursor to calculate the averages piecemeal. I don't see any good reason to do this, though.

Upvotes: 0

Related Questions