SQL Server query syntax problem

Hi can any one explain me what wrong with my query

SELECT    
     cDispatcherName AS Name, 
     cCallNature AS EmergencyCall, 
     COUNT(cCallNature) AS Total,
     (
        SELECT  
          开发者_JAVA百科   COUNT(cCallNature)   
             FROM   CallInfo  
             WHERE (cCallNature ='Regular call') 
        GROUP BY cCallNature
     ) AS Expr1
FROM         CallInfo 
WHERE     (cCallNature = 'Emergency call')
GROUP BY cDispatcherName, cCallNature

gives this error message:

Errormsg::There was an error parsing the query. [ Token line number = 1,Token line offset = 93,Token in error = SELECT ]

---

Its not entirely clear what your after from the original query, but you can't use nested selects in SQL-CE unless they return a set (as opposed to a scalar value) so instead try something like;

    SELECT    
       CallInfo.cDispatcherName AS Name, 
       cCallNature AS EmergencyCall, 
       COUNT(cCallNature) AS Total,
       SUB.CNT AS RegularCalls
    FROM CallInfo
    LEFT JOIN (
       SELECT  
          cDispatcherName,
          COUNT(*) AS CNT
       FROM  CallInfo  
       WHERE cCallNature ='Regular call'
       GROUP BY cDispatcherName
    ) AS SUB ON CallInfo.cDispatcherName = SUB.cDispatcherName

    WHERE    cCallNature = 'Emergency call'
    GROUP BY CallInfo.cDispatcherName, CallInfo.cCallNature, SUB.CNT

---

I think this query can be written in a better way but i can see a major problem with the sub query that you didn't specified the (DispatcherName).

So change the subquery as the following

 (
        SELECT  
             COUNT(cCallNature)   
             FROM   CallInfo rcall  
             WHERE (cCallNature ='Regular call' and rcall.cDispatcherName =CallInfo.CallInfo ) 

     ) AS Expr1 

Also you can change all the query (but this depends on your needs)

SELECT    
     cDispatcherName AS Name, 
     COUNT(cCallNature) AS EmergencyCallTotal,
     (
        SELECT  
             COUNT(cCallNature)   
             FROM   CallInfo rcall  
             WHERE (cCallNature ='Regular call' and rcall.cDispatcherName =CallInfo.CallInfo )
     ) AS RegularcallTotal
FROM         CallInfo 
WHERE     (cCallNature = 'Emergency call')
GROUP BY cDispatcherName