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

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

Mon, 19 Oct 2009 21:51:48 GMT

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

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

Anonymous — Mon, 19 Oct 2009 22:05:18 GMT

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.

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

Mon, 19 Oct 2009 22:08:27 GMT

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

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

Mon, 19 Oct 2009 22:46:36 GMT

Thanks, new it would be something simple! 🙂

Cheers for your help!

Joe