IIF Grouping Query Problem

[cbh35711]

Hello all,



Writing a query to take some data that is in a single column and partition it out by section. However, i'm hitting a problem that i cannot resolve. When i run the code below, it errors saying that i'm not including IIF(Section="1", sum(Breaks), null) in the group by. But that doesn't make any sense. My goal is to have one line with the sum(breaks) in each section(per AddDate, type).

select AddDate, Type,
IIF(Section="1", sum(Breaks), null) as [# of PD breaks], 
IIF(Section="2", sum(Breaks), null) as [# of Entries NOT in RA], 
IIF(Section="3", sum(Breaks), null) as [# of Entries NOT in CCR log] 
from trend_data
group by AddDate, type;

I can run this code, but it doesn't flatten the records into one. It just shows nulls in the additional columns, which is exactly what it should do.

select AddDate, Type, section
IIF(Section="1", sum(Breaks), null) as [# of PD breaks], 
IIF(Section="2", sum(Breaks), null) as [# of Entries NOT in RA], 
IIF(Section="3", sum(Breaks), null) as [# of Entries NOT in CCR log] 
from trend_data
group by AddDate, type,section;

Any advice would be greatly appreciated.

Thanks,

Chris

[cbh35711]

Had a thought... Sums on the outside of the IIF, not inside. Worker like a charm.

select AddDate, Type,
Sum(IIF(Section="1", Breaks, null)) as [# of PD breaks], 
sum(IIF(Section="2", Breaks, null)) as [# of Entries NOT in RA], 
sum(IIF(Section="3", Breaks, null)) as [# of Entries NOT in CCR log] 
from trend_data
group by AddDate, type;