Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: A quick counting question

    Once more I searched around but couldn't get a good handle on it.

    The problem is this:

    1) I send a notifier out to X no. of people
    2) Y no. of people click on the notifier
    3) I need to be able to get the percentage of Y/X

    table structure tblNotifier:

    OBJID
    Recipient
    Return_Status

    I can do it with 2 queries, but I'd love to do it with 1

    Select count(*) from tblNotifier;
    Select count(*) from tblNotifier where return_status=1;

    and then do the math in ASP, but.... really, that's just not a whole lot of fun.

    Thanks,
    Rob

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    how about : ---

    select sum (convert(numeric,Return_Status))/convert(numeric,count(recipient)) *100 from tblNotifier
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Posts
    17
    Originally posted by Enigma
    how about : ---

    select sum (convert(numeric,Return_Status))/convert(numeric,count(recipient)) *100 from tblNotifier
    'k How much does an explanation of why that works cost?


    Thanks, Rob

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select
    sum (convert(numeric,Return_Status)) --- Take the return status (1 for those who have replied , 0 for not replied) and sum it up
    / --- division
    convert(numeric,count(recipient)) --- no of rows

    *100 --- for percentage purposes

    from tblNotifier --- Your table

    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2003
    Posts
    17
    I'm just an ass evidently.. 20 seconds of thought and I see why that works. All apologies

    And thank you very much for pointing out my intellectual shortcomings

    Rob

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Again ..
    In case Return_Status is int ... you can simply say ...

    select sum (Return_Status)/convert(numeric,count(recipient)) *100 from tblNotifier

    though i assume it should be of type bit
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Nov 2003
    Posts
    17
    I'm just straight summing it becuase it is of type int. I was worried that they would add some random criteria for return_status, and have 3 or 4 different status options. So this way, it's not a big pain to add another table and reference it in.

    But it works great, so thanks a bunch.

    Rob

Posting Permissions

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