Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009

    Unanswered: Strange Distinct Issue

    Have the following data in table

    Fields = Location, Total, Date

    London | 2000 | 2/2/2010
    London | 3000 | 4/5/2010
    New York | 3000 | 3/4/2011
    France | 4000 | 4/5/2011

    SELECT DISTINCT Location, SUM(Total) AS Total, DATENAME(yyyy, Date) AS Year
    FROM Table
    GROUP BY Location, Date

    I can't get the data to come out like the following below. It keeps giving me 2 seperate rows for London eventhough i have put in Distinct

    London 5000 2010
    New York 3000 2011
    France 4000 2011

    Any help appreciated

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    You probably should not mix DISTINCT and GROUP BY. They do mostly the same thing. When you specify both, it may try to do both, which is bad for performance.

    Your GROUP BY clause is grouping rows based on the date in the table, not in the date column you have specified in your SELECT clause. Try this:
    SELECT Location, SUM(Total) AS Total, DATEPART(yyyy, Date) AS Year
    FROM Table
    GROUP BY Location, DATEPART(yyyy, Date)

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    /probably/definitely/ | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2009
    Many thanks MCrowley

    I didn't know you could do that in the group by

    Works Perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts