多个子查询

是否可以从同一个表的日期记录中得到如下结果：

               Enrolled   Enrolled as Email  Enrolled as Text Deals Redeemed   
<First Date>   7          5                  2                6
<Next Date>    9          3                  6               14

表结构看起来像这样：

Customer_id, field1, field2, responsecode, created_date

我当前的查询是这样的：

Select 
   Created,
   Enroll = (Select COUNT(*) from tblCustomer where field1 <> '' group by created),
   Email = (Select COUNT(field1) from tblCustomer where field1 = 'E-mail' and field1     <>    '' group by created),
   Cell = (Select COUNT(*) from tblCustomer where field1 = 'Cell Phone' and field1 <> ''    group by created)
from tblCustomer 
group by created 
order by created

您不需要 COUNT()，而是 SUM( CASE/WHEN )

Select 
      Created, 
      count(*) TotalCnt,
      SUM( CASE WHEN Field1 = 'E-mail' then 1 else 0 END ) as EMailCnt,
      SUM( CASE WHEN Field1 = 'Cell Phone' then 1 else 0 END ) as CellCnt,
      SUM( CASE WHEN RedeamedCondition then 1 else 0 END ) as RedeamCnt
   from 
      tblCustomer  
   group by 
      created  
   order by 
      created 

注意...如果创建的是日期/时间，您将需要仅根据“创建”的日期部分进行分组，否则每秒都会得到不同的计数...从另一篇文章中，以下内容得到仅删除日期时间的日期部分，基本上删除小时：分钟：秒部分

DATEADD(dd, 0, DATEDIFF(dd, 0, created)) 

或者如果这没有意义，你可以这样做

datepart( yy, created) as GrpYear,
datepart( mm, created) as GrpMonth,
datepart( dd, created) as GrpDay,  ... rest of columns.....