We help IT Professionals succeed at work.

ALL with GROUP BY - SQL Server

Hi,

Can you please explain me with simple example on - ALL with GROUP BY in SQL Server ? Why we need that and how to use it ? Please do not provide MSDN.

Thanks
Comment
Watch Question

Author

Commented:
Can you put it in two sentances ? I need why we are using it - (Point-to-Point).

Thanks
Take this sample
create table tempsalary
(
empid int,
salary int
)

insert into tempsalary values(1,1)
insert into tempsalary values(1,3)
insert into tempsalary values(1,2)
insert into tempsalary values(1,1)

Group by sample

select empid , sum(salary) as salary from tempsalary group by empid

OutPut be One record
Empid salary
1         7

In this case, I'm trying to get employee with the total salary so far they earned, If the number of employess is 100 and they salary detailed for every moth will go fo an new record and the above querry will return only 100 records regardless of the number of records in the table

Group by ALL
It's nothing but getting distinct records
select empid , salary as salary from tempsalary group by empid , salary
select distinct empid , salary from tempsalary

It only returns the distinct of the  records

Output be
EmpId   Salary
1      1
1      2
1      3


let me know, do you need more clarification
Top Expert 2012

Commented:
>>Why we need that and how to use it ? <<
That clause is deprecated.  That should be a compelling enough reason not to use it.  There are far better alternative apporaches.

Author

Commented:
santhimurthyd:

I did not understand what you did. May be your example is little bit complicated than it would be.

Thanks
if you try to querry with all the columns in Group by, it's like quering the distinct record
Group by ALL

select empid , salary as salary from tempsalary group by empid , salary

select distinct empid , salary from tempsalary

In my sample, I have records with duplicate entries and thus to show as the Group by and distinct will return the same number of records

If i'm taking the discussion away from you point of view, Please point out as where i'm moving away from the discussion.

Author

Commented:
santhimurthyd:

There is an option called as:

GROUP BY ALL

in SQL Server. Please check that and let me know - regarding your comments above.

Thanks

Author

Commented:
Need some explanation.