locked
Simple GROUP BY, COUNT-- I need another pair of eyes RRS feed

  • Question

  • I must be missing something simple.  I've been staring at the code below for a while and it isn't working correctly.  Here's what I need.  I have a table containing data associated with orders/quotes.  Each line has an associated currency.  I need to count the number of currencies in a give order.  Simple enough, right?   Well, the code below is returning the total number of rows before the GROUP BY statement.  Why wouldn't this work?  I'm stumped.

    Code Snippet

    SELECT COUNT(Currency) FROM QuoteGroupDetail
    WHERE QuoteNum = 'BMRH08-0020' AND Rev = '02' /*Order Keys*/

    AND QGrpID = 1 /*A Specific Group Key*/

    AND Inactive = 0 AND IsComment = 0 /*Deleted Rows and Comments are ignored*/

    AND Currency IS NOT NULL /*Not necessary, but for safety sake*/

    GROUP BY Currency


    --This If this were a SELECT * Query with the given criteria, 

    -- two rows are returned with identical Currency values.



    Also, I've executed the above code and without the GROUP BY and COUNT(), displaying all fields just to be sure that my filter wasn't grabbing any extra, unintentional rows.  It resulted in what I expected.  Additionally, if I execute the above statement to display only Currency field, instead of the COUNT(Currency), I get back the one currency that appears in the hard-coded example above.  Why can't I, in this case, get the COUNT() function to return the correct value?

    Please note that the comments in the code have been added for explanation
    Wednesday, November 26, 2008 6:49 PM

Answers

  • Try adding a Distinct to the Count.

     

    SELECT COUNT(DISTINCT Currency)

     

    Wednesday, November 26, 2008 6:54 PM

All replies

  • Try adding a Distinct to the Count.

     

    SELECT COUNT(DISTINCT Currency)

     

    Wednesday, November 26, 2008 6:54 PM
  • Thanks! That did it.  I didn't know you had to do that.
    Wednesday, November 26, 2008 7:12 PM