Pfthb

I have two tables, one storing enquiries (e_key) and one storing events attached to that enquiry. These Events have lengths of time assigned to them. I want to add up the total time attached to an enquiry and then use a CASE to assign it to one of three size categories, which I am using as an x axis on a bar chart.

I am using this to set up a Dimension field on the charting software we integrate with as my select:

CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Light Touch' WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 THEN 'b. Medium Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' ELSE 'Unspecified' END

and I get 'an aggregate function is not valid in the context in which it was invoked'. This is because when the select is copied into the GROUP BY it doesn't like the use of SUM().

This is the full SQL the chart is generating (e_key count on y axis, size band on x axis):

SELECT COUNT(distinct(Enquiry.E_KEY)) AS S1, CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Low Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 THEN 'b. Medium Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' ELSE 'Unspecified' END AS D1
FROM 
 DB2ADMIN.F_ENQUIRY Enquiry
 LEFT JOIN DB2ADMIN.F_EVENTS Events ON Enquiry.E_KEY = Events.EVENT_KEY
 JOIN DB2ADMIN.F_PRIORITY EnquiryType ON Enquiry.E_PRIORITY_LINK = EnquiryType.PRIORITY_NO
WHERE 
 (EnquiryType.PRIORITY_NAME = 'Enquiry' ) 
AND (Enquiry.e_job_type_link=0)
AND (Events.EVENT_TYPE=2)
GROUP BY CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Low Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 THEN 'b. Medium Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' ELSE 'Unspecified' END
ORDER BY CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Low Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 THEN 'b. Medium Intensity' WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' ELSE 'Unspecified' END

I cannot separate the SELECT from the GROUP BY section using the interface. Any ideas on different ways to achieve this? If I set the Type to Measure rather than Dimension it works as expected but then I cannot use it on an x axis. If I set up the same field excluding SUM() around EVENT_TIME_SPENT it allows me to set Type to Dimension and still work.

You don't need aggregates in the GROUP BY.

As it is, you are only wanting one row back right?

SELECT COUNT(distinct(E_KEY)) AS S1
, CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Low Intensity' 
 WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 
 THEN 'b. Medium Intensity'
 WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' 
 ELSE 'Unspecified' END AS D1
FROM 
 TABLE(VALUES(1,1),(1,2),(2,2)) AS Events (E_KEY, EVENT_TIME_SPENT)

which returns

 S1 D1
 -- -----------------
 2 c. High Intensity

if you want to group by something, then e.g. this works

SELECT E_KEY AS S1
, CASE WHEN SUM(Events.EVENT_TIME_SPENT) < 1 THEN 'a. Low Intensity' 
 WHEN SUM(Events.EVENT_TIME_SPENT) >= 1 AND SUM(Events.EVENT_TIME_SPENT) < 3 
 THEN 'b. Medium Intensity'
 WHEN SUM(Events.EVENT_TIME_SPENT) >= 3 THEN 'c. High Intensity' 
 ELSE 'Unspecified' END AS D1
FROM 
 TABLE(VALUES(1,1),(1,2),(2,2)) AS Events (E_KEY, EVENT_TIME_SPENT)
GROUP BY
 E_KEY


 S1 D1
 -- -------------------
 1 c. High Intensity
 2 b. Medium Intensity

or if you don't want to SUM() you EVENT_TIME_SPENT by anything, you might be after this

SELECT COUNT(DISTINCT E_KEY) AS S1
, CASE WHEN Events.EVENT_TIME_SPENT < 1 THEN 'a. Low Intensity' 
 WHEN Events.EVENT_TIME_SPENT >= 1 AND Events.EVENT_TIME_SPENT < 3 
 THEN 'b. Medium Intensity'
 WHEN Events.EVENT_TIME_SPENT >= 3 THEN 'c. High Intensity' 
 ELSE 'Unspecified' END AS D1
FROM 
 TABLE(VALUES(1,1),(1,2),(2,2),(2,0),(1,4)) AS Events (E_KEY, EVENT_TIME_SPENT)
GROUP BY
 CASE WHEN Events.EVENT_TIME_SPENT < 1 THEN 'a. Low Intensity' 
 WHEN Events.EVENT_TIME_SPENT >= 1 AND Events.EVENT_TIME_SPENT < 3 
 THEN 'b. Medium Intensity'
 WHEN Events.EVENT_TIME_SPENT >= 3 THEN 'c. High Intensity' 
 ELSE 'Unspecified' END

 S1 D1
 -- -------------------
 1 a. Low Intensity 
 2 b. Medium Intensity
 1 c. High Intensity