0

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
1
  • 3
    It would be better if you included the whole query. Aug 7, 2015 at 13:04

3 Answers 3

1
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
1
  • 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
    – Martin
    Aug 11, 2015 at 1:17
1

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
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.