SQLTeam.com | Weblogs | Forums

Subquery not working with aggregate function


#1

Hi,
Using a user defined function for listsplitter to manage the
comma separated list argument. When used along with aggregate function,
it shows message as 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

Sample code below

 Declare @specTable table(prodID int,prodName varchar(30),specName varchar(100), specValue varchar(100))

Insert into @specTable values
(1,'Sink1','Shape','Round')
,(1,'Sink1','Color','White')
,(1,'Sink1','Style','Pedestal')

,(2,'Sink2','Shape','Square')
,(2,'Sink2','Color','Black')
,(2,'Sink2','Style','Wallmount')

,(3,'Sink3','Shape','Round')
,(3,'Sink3','Color','White')
,(3,'Sink3','Style','Pedestal')


,(4,'Sink4','Shape','Rectangle')
,(4,'Sink4','Color','White')
,(4,'Sink4','Style','Pedestal')

,(5,'Sink5','Shape','Round')
,(5,'Sink5','Color','green')
,(5,'Sink5','Style','Pedestal')



Select * from @specTable


select prodid
  from @spectable
 group by prodid
 having sum(case when specname='Color' and specvalue='White' then 1 else 0 end)>0
and sum(case when specname='Shape' and specvalue in 

	( select value from ListSplitter('Round,Rectangle'))
	 then 1 else 0 end)>0

#2

You can't use a subquery in the HAVING. Try it like this:

and sum(case when specname='Shape' and exists(select 1 from dbo.ListSplitter('Round,Rectangle') where value = specvalue) then 1 else 0 end) > 0