Link to home
Start Free TrialLog in
Avatar of gudii9
gudii9Flag for United States of America

asked on

alternate to group by

SELECT  department, COUNT (Professor_Name ) from SALARIES  GROUP BY Department

how to write above query without using GROUP BY

please advise
ASKER CERTIFIED SOLUTION
Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can't.  Whenever you have an aggregate such as SUM or COUNT, unless this aggregate is performed on the entire table you need to define the columns which you want to group on in both the SELECT and GROUP BY clauses.  In your case that is department.

If you wish to COUNT for the entire salaries table then you can lose the GROUP BY clause.

This also begs the question 'Why do you want to remove the GROUP BY' ?
You could change it from an aggregate to a windowed aggregate

select distinct department, count(professor_name) over (partition by department order by department) from salaries

Probably doesn't perform as well, but it could be done that way.  Technically, it is really still doing a group by, but it doesn't actually have the group by clause in it.

FYI - you are posting a lot of questions into 2 different database areas (Oracle and SQL Server), the answers are not always going to be the same for both databases.  You really should just post to the one you are using.
why ?
what is the point ?
Avatar of gudii9

ASKER

unless this aggregate is performed on the entire table
any examples or links on this

please advise
Examples of aggregate on entire table:

select count(1) from salaries;
select avg(salary) from salaries;
select sum(salary) from salaries;
select min(salary), avg(salary), max(salary) from salaries;

You are only selecting the aggregate columns, so there is no grouping to be done.