none
Query Help Please RRS feed

  • Question

  • I have a table that contains a list of resources and their assigned manager as well as other details.  I want a query that will summarize the data to the manager level.  In the location values of HYD and PUNE should be considered OFF (offshore), NY and NJ (onshore) for purposes of the count.  So I am try to answer how many resources does a given manager have assigned, and of those how many are on/off shore, and are how many are SMEs. 

    Can this be accomplished in 1 query?

    Here is how the data would look:

    Resource  Manager      Location   SME 
    John        Tom             HYD        TRUE 
    Harry       Brian           PUNE       FALSE 
    Sam         Jill              NY           FALSE 
    Mary         Jack           NJ           TRUE 
    Bill           Jill              NY           FALSE 

    Desired result set:

    Manager   ResCount OnCount  OffCount SMECNT 
    Brian        1             0            1            0 
    Jack         1             1            0            1
    Jill            2             2            0           0
    Tom         1             0            1           1

    Tuesday, December 27, 2016 5:02 PM

All replies

  • Hi,

    You could maybe try something like:

    SELECT Manager, Count(Resource) As ResCount,
      Sum(IIf(Location="NY" OR Location="NJ",1,0)) As OnCount,
      Sum(IIf(Location="HYD" OR Location="PUNE",1,0)) As OffCount,
      Sum(IIf(SME=True,1,0)) As SMECount
    FROM TableName
    GROUP BY Manager

    (untested)

    Hope it helps...

    Tuesday, December 27, 2016 5:09 PM