need help in aggregate select

Question

Hi, 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 
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

Answer 1

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