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

Question

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 ]

'7/11/2011 12:00:00 AM' is not a safe format for a datetime literal - depending on settings, SQL Server may interpret that date as 7th November or 11th July. '2011-07-11T12:00:00' would be safely, unambiguously, 11th July. Also, if you want time to default to midnight, you can omit the time portion - use '20110711' in that case. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 5, 2011 at 6:38
@Damien - thx, I never knew that. Do you have a reference at hand backing that up? — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jul 5, 2011 at 6:42

Martin · Accepted Answer · 2011-07-05 06:59:21Z

2

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

edited Jul 5, 2011 at 6:59

answered Jul 5, 2011 at 6:27

Martin

1,5363 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KV Prajapati · Accepted Answer · 2011-07-05 06:31:42Z

1

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

answered Jul 5, 2011 at 6:31

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related