Difference between distinct and group by

Posted on 2005-04-11
Last Modified: 2012-05-05

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

Question by:hasozduru
    LVL 10

    Expert Comment

    You can go here to learn and know about it:

    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.
    LVL 6

    Assisted Solution

    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.

    LVL 8

    Expert Comment


    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

    LVL 14

    Expert Comment

    by:Renante Entera
    Hi hasozduru!

    For your reference, visit the following sites :

    Group by -
    Selecting Particular Columns -
    How MySQL Optimizes DISTINCT -

    Hope this helps you.

    eNTRANCE2002 :-)
    LVL 7

    Accepted Solution

    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.


    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.
    LVL 2

    Assisted Solution

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now