Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group By Problems - error msg "Aggregation expressions required by GROUP BY clause"

Hi,

I am trying to produce a report showing the number of calls answered and the number of calls abandoned,

I am running an SQL Database located on a server which i am able to connect to using an OLE DB connection, the connection works fine. The issue i am having is when i try to group by the call idenfiter "Call ID", i am gettin an error msg stating "Aggregation expressions required by GROUP BY clause"? What does this mean?

Each CallID apears in many records giving the actions the call has been through. So CallID 1 may be in 3 records.

Call ID Action

1 - Initialized

1 - Waiting

1 - Connected.

Here is the code i am using below:

SQL SELECT CallID, Action
FROM CCDS.dbo.InboundCallTracking;
LOAD [CallID], [Action]
RESIDENT InboundCallTracking
GROUP BY [CallID], [Action];


I have some examples coded in this way which work with no problems so am unsure why mine wont work?

Any help would be greated appresiated.

Thanks, Joe

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Joe,
Using GROUP BY is needed when you have aggreagtions like sum(), count(), max() in your SELECT or LOAD. For example:


SELECT
A,
B,
sum(C) as C,
min(D) as D
FROM table
GROUP BY A,B;

The rule is - the field must be either in aggregation (C and D here), or in GROUP BY (A, and B).
If there is no aggregation, as in your case - GROUP BY can't be used.

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Joe,
Using GROUP BY is needed when you have aggreagtions like sum(), count(), max() in your SELECT or LOAD. For example:


SELECT
A,
B,
sum(C) as C,
min(D) as D
FROM table
GROUP BY A,B;

The rule is - the field must be either in aggregation (C and D here), or in GROUP BY (A, and B).
If there is no aggregation, as in your case - GROUP BY can't be used.

Not applicable
Author

When you use a group by statement you need to summarize a field by using sum, avg, count,etc..

SQL SELECT CallID, Action FROM CCDS.dbo.InboundCallTracking;

LOAD count([CallID]) as [Number of calls], [Action]

RESIDENT InboundCallTracking GROUP BY [Action];

In this example you'll have tu count of callid per actions.

To optimize it you could add your Load statement before your SQL:

[Number of calls per actions]:

LOAD count([CallID]) as [Number of calls], [Action]

group by Action;

SQL SELECT CallID, Action FROM CCDS.dbo.InboundCallTracking;

I hope this will help you.

Rgds,

Sébastien

Not applicable
Author

Thanks, new it would be something simple! 🙂

Cheers for your help!

Joe