total Impression for same id and name

[r aa j]

hi here I am using SQL SErver 2005 with following query..... select distinct dbo.AE_Site.siteId as siteId,dbo.AE_Site.siteName as siteName,dbo.AE_Dimension.Impression as Impression from AE_Dimension inner join AE_Channel on dbo.AE_Dimension.channelId=dbo.AE_Channel.channelId inner join AE_Site on dbo.AE_Channel.siteId=dbo.AE_Site.siteId where dbo.AE_Dimension.cpm='0.5$' output is siteId siteName Impression 301 msn india 10 301 msn india 15 336 Yahoo India 45645 but I want output like siteId siteName Impression 301 msn india 25 336 Yahoo India 45645 anybody plz reply me ASAP.... Thanks

Rajendran.AL

[Blue_Boy]

select distinct dbo.AE_Site.siteId as siteId,dbo.AE_Site.siteName as siteName,(select sum(dbo.AE_Dimension.Impression) from dbo.AE_Dimension where dbo.AE_Dimension.chanelID in (select dbo.AE_Channel.channelId from AE_Channel)) where dbo.AE_Channel.siteId in (select dbo.AE_Channel.siteID from AE_Channel) and dbo.AE_Dimension.cpm='0.5$' hope it helps to you...

---

I Love T-SQL

[andyharman]

Try:

select S.siteId, S.siteName, sum(D.Impression) as Impression
  from dbo.AE_Dimension as D
  inner join dbo.AE_Channel as C
    on D.channelId = C.channelId 
  inner join dbo.AE_Site as S
    on C.siteId = S.siteId
  where D.cpm = '0.5$'
  group by S.siteId, S.siteName

Note that using table-aliases (e.g. "D" instead of "dbo.AE_Dimension") makes it more readable.

[Rajkamal_dfine]

Use group by siteId ...

N.Rajakumar B.E.,