Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Help with Group by Clause

    Hi Guys,

    I have a table.
    The Columns are Key1,Key2,Measure1,Measure2,Measure3.

    Measure1,Measure2 and Measure3 are all numeric Values.

    I need output in the following format

    Key1,Key2,(count of Measure1<1),(Count of Measure1>1.33),(count of Measure1 between 1 and 1.33)...

    Also Measure1 can have null values which we need to ignore during the count.

    And similarly for Measure2 and Measure3.

    I hope i made myself clear.

    Thanks For your help.
    Cheers!!!!

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Quick hint
    SUM(CASE WHEN m1<1 THEN 1 ELSE 0 END)

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I hope i made myself clear.
    IMO, at best it is ambiguous & at worst it is nonsensical.

    >Key1,Key2,(count of Measure1<1)
    is COUNT for KEY1, KEY2, both KEY1 & KEY2, or either KEY1 or KEY2?
    what is the relationship between KEY1 & KEY2 in line above? are they from same or different records in the table?
    Last edited by anacedent; 11-07-13 at 10:45.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Key1 and Key2 are columns, I would therefore assume that they are from the same row and the count is for the combination of the two columns.
    Of course, I could be wrong, but that's what it looks like to me.

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    @Pablolee,, Yes, for the combination of Key1 and Key2, the count of the Measure 1 with the given constraint has to be applied..
    And i tried the Sum(Case...) but it was throwing an error.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by mac4rfree View Post
    @Pablolee,, Yes, for the combination of Key1 and Key2, the count of the Measure 1 with the given constraint has to be applied..
    And i tried the Sum(Case...) but it was throwing an error.
    OK, what other information do you think might be useful in this situation (or do you think that i'm looking over your shoulder watching what you do on your screen) Boo!

Posting Permissions

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