problems forming a data mining query.

[pmatah]

i have three tables.

a CLIENTS Table:
clientid
firm# - firm the client belongs to

an OBJECTS table:
object#
clientid
#occurences

a DETAILS table:
object#
description - of the object
order# - the order in which the result needs to be displayed
flag - contains a 1 or a null

Need to retrieve description and total no. of occurences of the object for a paticular firm which would be simple enough as in :

select c.[order#],c.[description], Occurrences = sum([#occurrences])
from Clients a(nolock), [Objects] b(nolock),[Details] c(nolock)
where a.firm#=11000
and a.clientid = b.clientid
and b.[object#]= c.[object#]
group by c.[order#],c.[description]
order by c.[order#],c.[description]

BUT the problem here is that the SUM should have one added to it if flag(in the details table) contains a 1 otherwise #occurences from the OBJECTS table need to be added to the sum. I need some clues here.

 

[fluteplr]

look at the case statement i.e

sum(case flag when 1 then 1 else #occurences end)