• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1848
  • Last Modified:

Difference between distinct and group by

Hi

Could you please tell me the difference between using distinct and group by in the query?

Thanks
0
hasozduru
Asked:
hasozduru
3 Solutions
 
kupra1Commented:
You can go here to learn and know about it:
http://dev.mysql.com/doc/mysql/en/index.html

In a nutshell, distinct will return you distnct data i.e. no duplicate records for the field for which you have applied the word distinct data whereas the "Group By" is wider. It returns one record per group i.e based on the field on which you have applied the "Group By" clause.
0
 
peyoxCommented:
The main purpose of GROUP BY is to group records and then perform some calculations on these groups (SUM, AVG, etc.). However, if properly used both DISTINCT and GROUP BY will return the same recordset (when group by has nothing to agregate).

If you are looking for distinct values use DISTINCT, it clearly states your intentions, efficiency is probably the same since DB is "smart" enough to optimize your query.
Use GROUP BY when you need to do more with your records (groups) - calculating averages, sums etc. This DISTINCT can not do.

Cheers,
peyox
0
 
nagkiCommented:
Hi..

that ditinct functionality u can achieve through group by..see the following example..

select distinct col1,col2 from Table..

The above select statment can be re-written as

select col1,col2 from Table group by col1,col2

Hope this helps



0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Renante EnteraSenior PHP DeveloperCommented:
Hi hasozduru!

For your reference, visit the following sites :

Group by - http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html
Selecting Particular Columns - http://dev.mysql.com/doc/mysql/en/selecting-columns.html
How MySQL Optimizes DISTINCT - http://dev.mysql.com/doc/mysql/en/distinct-optimization.html

Hope this helps you.


Goodluck!
eNTRANCE2002 :-)
0
 
Mehul ShahIT consultantCommented:
Well group by and  distinct has its own use.

Distinct is used to filter unique records out of the records that satisfy the query criteria.

Group by clause is used to group the data upon which the aggregate functions are fired and the output is returned based on the columns in the group by clause. It has its own limitations such as all the columns that are in the select query apart from the aggregate functions have to be the part of the Group by clause.

So even though you can have the same data returned by distinct and group by clause its better to use distinct. See the below example

select col1,col2,col3,col4,col5,col6,col7,col8,col9 from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9

can be written as

select distinct col1,col2,col3,col4,col5,col6,col7,col8,col9 from table

It makes you life easier when you have more columns in the select list. But at the same time if you need to display sum(col10) along with the above columns than you will have to use Group By. In that case distinct will not work.

eg

select col1,col2,col3,col4,col5,col6,col7,col8,col9,sum(col10) from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9

Hope this helps.
0
 
Marvin_CarredoCommented:
The DISTINCT clause allows you to remove duplicates from the result set.
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now