Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: >=1000000 Query Fails

    I'm racking my brain.

    I'm trying to sum sales of $1,000,000 and above only, by ListName.

    This query is counting sales below $1,000,000 also.

    What am I missing here?

    Code:
    SELECT DISTINCTROW Sum([SCMLS Query].SalePrice) AS SumOfSalePrice, 
    Avg([SCMLS Query].SalePrice) AS AvgOfSalePrice, 
    Count([SCMLS Query].SalePrice) AS CountOfSalePrice, [SCMLS Query].ListName
    FROM [SCMLS Query]
    GROUP BY [SCMLS Query].ListName
    HAVING (((Sum([SCMLS Query].SalePrice))>=1000000))
    ORDER BY Sum([SCMLS Query].SalePrice) DESC;
    Thanks much . . . Rick

  2. #2
    Join Date
    Sep 2004
    Location
    Bahrain
    Posts
    21
    Hi Rick

    What let you know that the result was wrong?

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Because the totals are wrong when I look at the data. The query is including sales under $1,000,000 and limping that into the sum.

    My goal is to sum sales of $1,000,000 and above and list that sum by office name.

    I'm close but missing something really lame I'm sure.

    Thanks . . . Rick

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    A Simpler Version Fails

    This does not select sales >=$1,000,000.

    It is still including sales under this amount in the sum.

    Any ideas would be greatly appreciated.

    Rick

    Code:
    SELECT DISTINCTROW [SCMLS Query].ListName, 
    Sum([SCMLS Query].SalePrice) AS [Total Dollar Volume]
    FROM [SCMLS Query]
    GROUP BY [SCMLS Query].ListName
    HAVING (((Sum([SCMLS Query].SalePrice))>=1000000))
    ORDER BY Sum([SCMLS Query].SalePrice) DESC;

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Can you give us a sample for the data your processing?

    though of the top of my head if the query is working but giving you the wrong results

    HAVING (Sum([SCMLS Query].SalePrice)>=1000000

    must be the problem try

    HAVING [SCMLS Query].SalePrice>=1000000
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think your problem lies in "DISTINCTROW". Do away with that.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    if the the distinct row was the problem wouldn't it stop the query's calculations working right, not have them working right but on too many records?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    It is still including sales under this amount in the sum.
    the problem is, you haven't filtered out the individual amounts that are below 1000000

    that's what the WHERE clause is for
    Code:
    SELECT ListName
         , Sum(SalePrice) AS [Total Dollar Volume]
      FROM [SCMLS Query]
     WHERE SalePrice >= 1000000
    GROUP 
        BY ListName
    ORDER 
        BY Sum(SalePrice) DESC
    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that would've been my next suggestion if removing the sum command from the having hadn't worked
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    And the winner is . . .

    r937 . . .

    Rudy's suggestion worked the first time out of the box.

    I thank all of you.

    I'm learning at a much faster rate now.

    I really appreciate it.

    Rick

Posting Permissions

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