Results 1 to 8 of 8

Thread: Count question

  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Count question

    Hi DBA's -

    Kindly help me figure out the following

    My data looks like this -

    Month Product Brand Revenue
    ------ ------- --------- --------
    Jan A x 10
    Jan A y 20
    Jan B z 30

    A report from the above data would be

    Revenue for Jan = 60 and Product count = 2.

    So I figure, I would need

    Month Product Brand Revenue Flag
    ------ ------- --------- -------- -----
    Jan A x 10 1
    Jan A y 20 0 (since A is counted)
    Jan B z 30 1

    I want to count only the first occurence of the product in the Flag column.

    Is there a way to do this.

    - Vivek

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	Month,
    	sum(Revenue),
    	Count(distinct Product)
    from	YourTable
    group by Month
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select [month],
    productcount=count(distinct product),
    totalrevenue=sum(revenue)
    from your_table
    groupby [month]
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    hey, I just didn't click on Submit, because there other things in life, like phone calls!!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Phone calls?

    That's OLD TECHNOLOGY...

    Your code was more complete, anyway. I confess I was being a little lazy...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    But did you notice the snippets are almost identical? This is earie...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are just saying that to be nice. Yours was much more colorful than mine as well. I simply lack your aesthetic sense of code.

    Mine was pathetic. A shoddy hack of garbled syntax totally lacking in character or depth. In my haste to post, I neglected that which makes code enjoyable and pleasing to the senses.

    I am truly ashamed.

    Wait a minute... "Groupby"?

    Hey! "Group by" is two words, not one! That won't even compile, much less execute!

    Hmmph! Well. I guess I feel better now.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Now who's a real hoot?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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