请查询帮助 [英] Query Help Please

问题描述

我有一个表格，其中包含资源列表及其分配的经理以及其他详细信息。 我想要一个将数据汇总到经理级别的查询。 在计算时，HYD和PUNE的位置值应视为OFF（离岸），
NY和NJ（境内）。 所以我试着回答一个特定经理分配了多少资源，以及有多少是在岸上/离岸，以及有多少是中小企业。 

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. 

这可以在1个查询中完成吗？

Can this be accomplished in 1 query?

这是数据的方法会看起来：

Here is how the data would look:

资源 经理     位置 中小企业 

John       汤姆             HYD        TRUE 

Harry      布赖恩            PUNE       FALSE 

Sam        吉尔              NY            FALSE 

玛丽        杰克           NJ           TRUE 

Bill          吉尔              NY           FALSE 

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

所需的结果集：

经理   ResCount  OnCount   OffCount  SMECNT 

Brian        1             0            1           
0 

Jack         1             1            0             1个

吉尔        &NBSP ;   2             2            0          
0

Tom         1             0             1           1

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

推荐答案

你也许可以尝试类似：

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

（未经测试）

希望它有所帮助......

Hope it helps...

这篇关于请查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！