SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

This question is asked many times to me. What is difference between DISTINCT and GROUP BY?

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Joins, SQL Scripts
Previous Post
SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON
Next Post
SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

Related Posts

96 Comments. Leave new

  • Blueprint RSA
    May 31, 2011 9:33 am

    Thanks for posting this very informational blog. Now I know the difference between DISTINCT and GROUP BY. I hope to achieve something from what I learn today.

    Reply
  • Hi Sally,

    You can do the following to get the results inon estatement.

    SELECT Col1,COUNT(*) AS Total FROM TMP
    GROUP BY Col1
    ORDER BY Col1

    Reply
  • I need your help ragarding sql query.

    Val_1, Val_2, Val_3,Val_4
    a b c 1
    a b c 2
    a b c 4
    m n o 1
    m n o 2

    and output should be

    val_1, Val_2,val_3, val_4
    a b c 1,2,4
    m n o 1,2

    Pls help if anyone know…

    Reply
  • Dear all i need a help from you people. I am using vb6 & ms sql server2000. I have two tables.

    1- Order (master table) having data

    ORDERID DATE EMPNAME
    1 1-2-1999 JOHN
    2 1-1-2010 JOHN
    3 1-12-2011 FOAD

    2-Orderdetail (child table) having data

    PRID PRNAME SP QTY TOTAL ORDERID
    11 CD 5 10 50 1
    11 CD 5 5 25 1
    12 DVD 10 5 50 2

    i wan to get data like this

    Empname Prname Qty SP total
    JOHN CD 15 5 75
    DVD 5 10 50

    Regards

    Reply
  • hi pinal,
    i find your blog very useful.
    i have been working for a airticketing service i have a table with 2 cols id and wsdate(datetime datatype) where wsdate contains date and time which got some thousands of records on different dates,i want to display distinct date col and other one is count of rows from the table on that particular date.please try to help me out

    Reply
  • JideshDavid
    June 3, 2012 12:18 am

    Thank u sir, I forgot Distinct Clause, to remove duplicate values…. At the right time your answer helps me…alot..

    Reply
  • Hi Pinal,
    can you tell me why count of distinct id’s [id’s are 0]=1?

    Reply
  • Ganesh Chaudhari
    June 6, 2013 5:42 pm

    Hi….
    I am facing one problem that when I fire above query it will give me multiple records for single ItemNo but I wanna only unique records. What I need to do for this?

    Select distinct tg.ItemNo,i.TaxIncExc,case WHEN g.TaxFrom=’Inside Maharashtra’
    THEN i.TaxInsideMaha Else i.TaxOutsideMaha END as TaxPercent,i.ItemName,
    ((tg.Qty*i.mrp)*(ISNULL(i.Excise,0)))/100 as Excise,tg.Price,’0′ as TAxAmt,tp.ItemQty,
    tg.Qty as AcceptedQty,tp.BalanceQty,” as ExpiryDate,tg.RejectedQty,
    (tg.Qty*i.mrp) as TotalPrice from trn_grn tg inner join mst_item i
    on i.itemno=tg.itemno inner join mst_grn g on g.grnno=tg.grnno inner join mst_employee e
    on e.empno=tg.inspector inner join mst_po p inner join Trn_PO tp on tp.PONo=p.PONo
    on p.pono=g.pono where g.potype=’Normal_PO’and g.pono=27

    Reply
  • Hello Pinal, I never opine but I constantly read your posts. Your contributions to the SQLers are priceless, I just wanted to take the time to say thank you!

    Reply
  • Hi colleagues, its fantastic piece of writing regarding educationand fully explained, keep it
    up all the time.

    Reply
  • your posts always prove to be of a high value and quality for readers.

    Reply
  • Pinal Dave, thank you for your postings; you have been extremely helpful. Whenever I have a question, I usually Type “Pinal Dave” followed by the question, You are always Spot ON. Thanks again for sharing the knowledge.

    Reply
  • Can we use Distinct with Group By in sub query ?

    Reply

Leave a Reply

Menu
Exit mobile version