mql4beginner mql4beginner - 4 months ago 14
SQL Question

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.

Answer

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_