need help in aggregate select

i have a problem with selecting some values from my DB. DB is in design stages so i can redesign it a bit of needed.

You can see the Diagram on this image

Basically what i want to select is

select 
开发者_StackOverflow中文版c.campaignID,
ct.campaignTypeName,
c.campaignName,
c.campaignDailyBudget,
c.campaignTotalBudget,
c.campaignCPC,
c.date,
cs.campaignStatusName
***impressions,
***clicks,
***cast(campaignTotalBudget-(clicks*campaignCPC) as decimal(18,1)) as remainingFunds 
from Campaigns as c
left join CampaignTypes as ct on c.campaignTypeID=ct.campaignTypeID
left join CampaignStatuses as cs on c.campaignStatusID=cs.campaignStatusID
left join CampaignVariants as cv on c.campaignID=cv.campaignID
left join CampaignVariants2Visitors as c2v on cv.campaignVariantID=c2v.campaignVariantID
left join Visitors as v on c2v.visitorID=v.visitorID
.....
order by c.campaignID desc

Problem is that Visitors table has column named isClick so i don't know the way to separate what is impression with isClick=false and what is click isClick=true so i can show nice form with all the stuff about campaign and visitors...

I don't think to split Visitors to two tables like Impressions and Click is a good idea because again i would need to have Visitors with two more tables

thanks

It is not clear what "impressions" are in your diagram. However, if you are wondering how to count clicks per campaign, you can do something like:

Select ...
From Campaigns As c
    ...
    Left Join (
                Select V1.VisitorId
                    , CV2V1.CompaignVariantID
                    , Sum( Case When V1.IsClick = 1 Then 1 Else 0 End ) As ClickCount
                    , Count(*) TotalVisitorCount
                From Visitors As V1
                    Join CompaignVariant2Visitors As CV2V1
                        On CV2V1.VisitorId = V1.VisitorID
                Group By V1.VisitorId, CV2V1.CompaignVariantID
                ) As VisitTotals
        On VisitTotals.VisitorId= C2V.VisitorId
            And VisitTotals.CampaignVariantID = C2V.CampaignVariantID
...
Order By c.campaignID desc