CODEWITHSUNDEEP
sqlsql-server
mql4beginner
mql4beginner

Reputation: 2233

Can I use sum with case statement on a max/min function?

I would like to sum the occurrence of a case.I tried to use the sum on the case statement but I get this error, how can it be solved?

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

My code example:

select 
sum(case when cast(max(ptl.RowDate)as int) = cast(Min(ptl.RowDate) as int) then 1 else 0 end)
,DATEPART(WEEK, rowdate) week_
from m.dbo.tblLog ptl (nolock)
where rowdate > GETDATE()-91
group by DATEPART(WEEK, rowdate)

Update: Based on comments: I would like to count distinct users that were visiting the webpage only once.I would like to get the counts based on weekly time frame.

Upvotes: 0

Views: 5086

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you just want a flag, why would you want a sum():

select (case when cast(max(ptl.RowDate)as int) = cast(Min(ptl.RowDate) as int) 
             then 1 else 0
        end) as OneValueFlag,
       DATEPART(WEEK, rowdate) as week_
from m.dbo.tblLog ptl (nolock)
where rowdate > GETDATE() - 91
group by DATEPART(WEEK, rowdate);

It is also entirely unclear why the value would be converted to a date:

select (case when max(ptl.RowDate) = Min(ptl.RowDate)
             then 1 else 0
        end) as OneValueFlag,
       DATEPART(WEEK, rowdate) as week_
from m.dbo.tblLog ptl (nolock)
where rowdate > GETDATE() - 91
group by DATEPART(WEEK, rowdate);

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Alternative way to your query 

SELECT SUM(CASE WHEN CAST(MaxRowDate AS INT) = cast(MinRowDate AS INT) THEN 1 ELSE 0 END)
week_
FROM
(

  SELECT MAX(ptl.RowDate) MaxRowDate,MIN(ptl.RowDate) MinRowDate,DATEPART(WEEK, rowdate) week_
  FROM m.dbo.tblLog ptl (NOLOCK)
  WHERE rowdate > GETDATE()-91
  GROUP BY DATEPART(WEEK, rowdate)
 )M   
 GROUP BY week_

Upvotes: 1

gofr1
gofr1

Reputation: 15977

You can not use aggregate functions inside another. You need to use sub-query or CTE:

SELECT  SUM(something) as s,
        week_
FROM (
    select  case when cast(max(ptl.RowDate)as int) = cast(Min(ptl.RowDate) as int) then 1 else 0 end as something,
            DATEPART(WEEK, rowdate) week_
    from m.dbo.tblLog ptl (nolock)
    where rowdate > GETDATE()-91
    group by DATEPART(WEEK, rowdate)
    ) as res
GROUP BY week_

Upvotes: 1

Related Questions