• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1832
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Renante EnteraCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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