Distinct vs. Group By

[SharonAnaki]

hi,

what is the difference between Distinct and Group By (in terms of the result)and when is it necessary to use both of them?

thanx

 

[cdukes]

It will depend on what your query is, what is in your table and what you are trying to do. If there are multiple records in your table with the same data ( eg personnelID ) then distinct will return only one record for each of the ID's. thus if you had the following table:

PersonnelId
1
2
3
2
4
4
5

with distinct you will get ID's (1,2,3,4,5).

Group By on the other hand allows you to perform aggregate queries on columns in the table. Take the above example and add a column for Salary which might comprise of a baseic salary (100) and a Bonus (50).

PersonnelId Salary
1 100
2 100
3 100
2 50
4 100
4 50
5 100

The group by allows you to sum the data according to the group by column
ie Select PersonnelId, sum(salary) from tblPersonnel
Group by PersonnelId
will give you:
1 150
2 150
3 100
4 150
5 100

Hope this helps,

Chris Dukes

 

[Cagliostro]

group by is for such functions as sum() count() avg() ... But distinct is jut for a distinct select even in some cases you can use group by instead of distinct. John Fill
ivfmd@mail.md