gudii9
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
how to write above query without using GROUP BY
please advise
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 ?
what is the point ?
ASKER
unless this aggregate is performed on the entire tableany 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.
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.
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' ?