Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: Conditional Counting

    I have a SQL database I am trying to extract data from and place a conditional counting formula in it.

    I am trying to count the instances of ID numbers in a table. Some of them can have two attributes associated with the ID numbers. I want to count each ID number for each attribute but I don't want to double count the ID number if it has both attributes associated with it.

    How do I go about doing this?

    Thanks for your help in advance,

    -Chris

  2. #2
    Join Date
    Nov 2003
    Posts
    54
    Can you provide a simple example data set?

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    This is a summary of the total instances of data.

    Indicator, Description, Goal, Avg, Met, Goal, Total Pool, Threshold, %,

    R-03, Average Time to Assign Property, 1 b-day, 0.3, 777.00, 812, 90%, 95.7%,

    R-03-H Average Time to Assign Property, 3 b-days, 1.3, 119.00, 129, 90%, 92.2%,

    I put commas in so you could make out the columns. All of the calcs need this assigned to them.

    As you can see there are two indicators.

    If an ID# had an R-03 indicator and then showed up again with an R-03-H indicator I only want to count the same ID in the R-03 and double count it.

    But if it only shows up with the R-03-H I want to count that one only.

    Thanks for you help

  4. #4
    Join Date
    Nov 2003
    Posts
    54
    Do you have a unique identifier for each row? It sounds like you have some sort of ID#, so I will abse my answer on that.

    In your crystal, set up a running total field that makes a distinct count of your ID#, and with the criteria that indicator in ['R-03', 'R-03-H']. Hope this helps.
    Last edited by cdols; 09-20-05 at 15:14. Reason: Typo

  5. #5
    Join Date
    Sep 2005
    Posts
    5
    Yes and No. The ID# is unique but can show up in both Indicators. How do I only count them once if they show up in each and still have a count for the H indicator that doesn't include the dups?

  6. #6
    Join Date
    Nov 2003
    Posts
    54
    By using the 'distinct count' in the running total field, you will count each ID# only once. Open up your crystal help and look up 'Running Total Fields' and it shoudl give you all the information that you need.

  7. #7
    Join Date
    Sep 2005
    Posts
    2
    Maybe I can be of assistance. Use the grouping by ID and then Distinct Count subtotal for each indicator. then sum the distinct counts in the report footer/header.

Posting Permissions

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