# Min(date) in sum case statement [closed]

I'm trying to write a statement where the settled date is less than the `min(duedate)` but I'm getting this error:

Msg 130, Level 15, State 1, Line 32
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is the query I'm using - can anyone help please?

``````...
sum(case when
cast(l.settleddate AS date) < min(cast(f.datedue as DATE))
then 1
else 0
end) as BeforeFirstDue
``````
• It would be better if you included the whole query. Aug 7, 2015 at 13:04

``````DECLARE @myDate AS datetime
SELECT @myDate = min(cast(f.datedue as DATE)) FROM tablef f
SELECT
sum(case when cast(l.settleddate AS date) < @myDate
Then 1
else 0
end) as BeforeFirstDue
FROM tablel l
``````
• use Aggregate function MIN() to get value from a set of data and then use another Aggregate function SUM() to get value base on the MIN() result in this syntax is not possible Aug 11, 2015 at 1:17

It's hard to give a proper answer without the full query. So let's pretend that your query looks something like this:

``````SELECT sum(case when
cast(l.settleddate AS date) < min(cast(f.datedue as DATE))
then 1
else 0
end) as BeforeFirstDue
FROM tableL l
JOIN tableF f
ON f.someKey = l.someKey
``````

If that's your case, here is one way you can rewrite your query to do what you want:

``````SELECT sum(case when
t.settleddate < t.mindatedue
then 1
else 0
end) as BeforeFirstDue
FROM (SELECT cast(l.settleddate AS date) AS settleddate,
min(cast(f.datedue as DATE)) OVER () AS mindatedue
FROM tableL l
JOIN tableF f
ON f.someKey = l.someKey) t
``````

You are mixing your granularity inside your CASE statement. You cannot compare an aggregate with a non aggregate unless they come from two different datasets