1 Reply Latest reply on Dec 26, 2016 9:56 AM by Yuriy Fal

    SQL Exists

    Mike Angell

      Hi everyone,

      I can not post a workbook, so hopefully my description is enough:

       

      Basically, I have a view where I am counting people in a group, there are many groups.  I want to be able to filter the view down to the number of groups that have had at least one occurrence of a particular activity.

       

      The activity is pretty rare.  And, I want to be able to keep the number of people in the total group, not simply report/show those that had that activity.

       

      For example - 10 people in group 1.  One had the activity I'm looking for. 

                                number of group                  m1mgoCount

      Group 1     .          10                                               1

       

      What I am getting, when I add a filter that says,

      if  [m1mgoCount] > 0

      Then 'True'

      else 'False'

      end

       

      And filter to "True"

       

      is

                            number of group              m1mgoCount

      Group1                 1                                           1

       

      So, how do I create the calc'd field, or is there any way to help the view to display all the people in the group, when only one in the group has done activity 3 (has a 1 in m1mgoCount field).

       

      Thank you in advance. We're on 10.0.2  This is SQL Server, and the m1mgoCount field is in a table that has a left join to it.

       

      Mike