Help with a count.

[HestonJames]

Hello Chaps,

I have the following query:

Select	Campaign.Campaign_ID,
		Campaign.Name,
		Event.ID,
		Media.Name As MessageName,
		Media.Type
From	Campaign
Left Join	Event On Campaign.Campaign_ID = Event.Campaign_ID
Left Join	Media On Event.Media_ID = Media.Media_ID
Where	Campaign.Owner_ID = 46
And		Campaign.Active = 1
Group By	Campaign.Campaign_ID,
			Campaign.Name,
			Event.ID,
			Media.Name,
			Media.Type

Now, If I wanted to return an additional column which gave me the count of records in the Event table for each Campaign object, how would I go about that? I've tried doing a 'Count(Event.ID) As NumberOfEvents' but that just gives me 1 for each row.

I'd appreciate your help

Heston

 

[gmmastros]

Do you really need to return the Event.Id from the query? I mean... I suspect there are multiple id's for each campaign (or else 1 would be the right answer).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I'm gonna go out on a limb and suggest this....

Select  Campaign.Campaign_ID,
        Campaign.Name,
        EventCounts.CampaignEventCount,
        Media.Name As MessageName,
        Media.Type
From    Campaign
        Left Join (
            Select Campaign_id, 
                   Count(*) As CampaignEventCount
            From   Event 
            Group By Campaign_id
            ) As EventCounts On Campaign.Campaign_ID = EventCounts.Campaign_ID
        Left Join    Media On Event.Media_ID = Media.Media_ID
Where   Campaign.Owner_ID = 46
        And Campaign.Active = 1

Please let me know how wrong I am.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hi George, thanks for the super speedy response. I could effectively use the ID as its used in a grouped output, however, its really not important, the count is far more important, does dropping the ID make is possible?

Thanks,

Heston

 

[HestonJames]

on a score from 1 to 10, with 10 being the most wrong you could be.... ;-)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Event.Media_ID" could not be bound.

Now, because that would suggest to me that the column Media_ID doesn't exist? however it does. What do you think the problem is?

Cheers my man,

Heston