Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: Query with GROUP BY bring up phantom records?

    Hello,

    I'm trying to design a query to show the total value of sales per year/month. It looks like this:

    Code:
    SELECT SUM(Payments.PaymentAmount) AS Amount, Year(Payments.PaymentDate) AS Year, Monthname(Month(Payments.PaymentDate)) AS Month
    FROM Payments
    GROUP BY Year(Payments.PaymentDate), Month(Payments.PaymentDate);
    It executes OK but the results look suspiciously wrong. It includes a line for August 1930 and lines for September to December 2007, although the dataset only includes entries from November 2003 - August 2007!

    I've checked the data and it looks fine. I ran this query to double check and it doesn't show the phantom records:

    Code:
    SELECT Year(Payments.PaymentDate), Month(Payments.PaymentDate), PaymentDate FROM Payments;
    Can anyone spot where I'm going wrong?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try cloning the table (so as not to mess up your live data), and then start removing rows from the cloned table until the phantom rows from the query disappear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    offhand / at first glance it looks ok...

    If be tempted to make sure I didnt use reserved words for aliases
    eg
    Code:
    SELECT SUM(Payments.PaymentAmount) AS Amount, Year(Payments.PaymentDate)  AS PmntYear, Monthname(Month(Payments.PaymentDate)) AS pmntMonth
    FROM Payments
    GROUP BY PmntYear,PmntMonth;
    id also be tempted to make the grouping to be a composite of the year and month, if that failed just to see if the SQL parser is is getting its knickers in a twist
    eg
    Code:
    SELECT SUM(Payments.PaymentAmount) AS Amount, Year(Payments.PaymentDate)  AS PmntYear, Monthname(Month(Payments.PaymentDate)) AS pmntMonth,
    format(Payments.PaymentDate,"YYYYMM")  AS PmntIndex
    FROM Payments
    GROUP BY PmntIndex;
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2007
    Posts
    5
    Thanks for the suggestions.

    I tried that second query and, surprise surprise, got this error: "You tried to execute a query that does not include the specified expression 'Year(Payments.PaymentDate)' as part of an aggregate function."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try my suggestion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Posts
    5
    r937, there are thousands of rows in the table, I'll try it if I don't come up with anything better by the end of the day! Thanks for the suggestion though...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by tomchance
    Thanks for the suggestions.

    I tried that second query and, surprise surprise, got this error: "You tried to execute a query that does not include the specified expression 'Year(Payments.PaymentDate)' as part of an aggregate function."

    what do you need the year & month values for.. they are derived from the payment date do effectively they can be retrieved form the pmntindex

    so that could be
    Code:
    SELECT SUM(Payments.PaymentAmount) AS Amount, 
    format(Payments.PaymentDate,"YYYYMM")  AS PmntIndex
    FROM Payments
    GROUP BY PmntIndex;
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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