CODEWITHSUNDEEP
sql-server
Ram
Ram

Reputation: 933

Sql query syntax problem.(system.data.sqlserverce)

Can any one please suggest me whats the wrong with my query.

SELECT SUM(cCallDuration), COUNT(*), AVG(cCallduration), 
       cBeginTime, cEndTime, cAnswerTime, cCallDuration, cDispatcherName, cConsoleName,
       cEndpointName, cProfileName, cCallDirection, cCallType,
       cCallNature, cCallData, cDirectedCall
FROM  CALLINFO 
WHERE cBeginTime >='7/11/2011 12:00:00 AM' 
     AND cEndTime <='7/11/2011 12:00:00 AM' 
     AND cCallType='InBound' 
GROUP BY cConsoleName

I'm getting this error:

In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,cBeginTime ]

Upvotes: 0

Views: 1033

Answers (2)

Martin
Martin

Reputation: 1536

You have to have more in your GROUP BY clause

Check out this example. Every column that is not aggregate is included in the GROUP BY. You must do that also

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

Try this one:

SELECT SUM(cCallDuration), COUNT(*), AVG(cCallduration), 
       cDispatcherName, cConsoleName,              
FROM  CALLINFO 
WHERE cBeginTime >='7/11/2011 12:00:00 AM' 
     AND cEndTime <='7/11/2011 12:00:00 AM' 
     AND cCallType='InBound' GROUP BY cConsoleName,cDispatcherName

so to complete your full sql syntax you have to include every column that is not an aggregate in your GROUP BY clause and that means every column except the SUM,AVG and COUNT

Upvotes: 2

KV Prajapati
KV Prajapati

Reputation: 94643

GROUP BY clause : Columns in any nonaggregate expression in the SELECT list must be included in the GROUP BY list.

SELECT SUM(cCallDuration), COUNT(*), AVG(cCallduration), 
       cBeginTime, cEndTime, cAnswerTime, cCallDuration, cDispatcherName, cConsoleName,
       cEndpointName, cProfileName, cCallDirection, cCallType,
       cCallNature, cCallData, cDirectedCall
FROM  CALLINFO 
WHERE cBeginTime >='7/11/2011 12:00:00 AM' 
     AND cEndTime <='7/11/2011 12:00:00 AM' 
     AND cCallType='InBound' GROUP BY 
        cBeginTime, cEndTime, cAnswerTime, cCallDuration, cDispatcherName, cConsoleName,
       cEndpointName, cProfileName, cCallDirection, cCallType,
       cCallNature, cCallData, cDirectedCall

Upvotes: 1

Related Questions