Results not showing. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Results not showing.

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-05 : 17:34:13

I have the follow query, but it is only returning 1 result for roll item 421111. Now if I run a simple query for just roll item 421111 for the same date and advertiser id, I get 2 different campaigns for that roll item, and our system shows 2 different ones too. Any idea?

SELECT agg.date AS StartDate,
agg.roll_item_id,
SUM(agg.impressions) AS Impression,
SUM(agg.clicks) AS Clicks,
SUM(agg.clicks)/SUM(agg.impressions) AS CTR,
SUM(agg.post_impressions+agg.post_clicks) AS Conversions,
io.name AS IOname,
roll.budget,
camp.start_time,
camp.end_time,
SUM(agg.impressions*roll.unit_price/1000)AS DaySpend,
roll.unit_price AS CPM,agg.io_id AS IO,
agg.advertiser_id,
io.goal_op,
camp.name AS Campaign,
io.goal_value

FROM agg_delivery_daily agg,bam_ios io,bam_roll_items roll,bam_campaigns camp

WHERE io.id=agg.io_id
AND roll.id=agg.io_roll_item_id
AND camp.id=agg.campaign_id
AND agg.advertiser_id IN ('111','123','3333')
AND agg.date='2011-01-01'
AND agg.roll_item_id=421111
GROUP BY io.name,agg.io_roll_item_id,roll.budget,camp.name
ORDER BY io.name,agg.date

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 19:06:32

If you are not grouping by date or advertiser id, that should not happen. Is your simple query just against one table, or does it join both tables as in the query you have below? Run this query and see how many you get. Here, I have simply removed the grouping and summation. If it returns only one, then it would be the join that is losing one of the rows.

SELECT agg.date AS StartDate,
agg.roll_item_id,
--SUM(agg.impressions) AS Impression,
--SUM(agg.clicks) AS Clicks,
--SUM(agg.clicks)/SUM(agg.impressions) AS CTR,
--SUM(agg.post_impressions+agg.post_clicks) AS Conversions,
io.name AS IOname,
roll.budget,
camp.start_time,
camp.end_time,
SUM(agg.impressions*roll.unit_price/1000)AS DaySpend,
roll.unit_price AS CPM,agg.io_id AS IO,
agg.advertiser_id,
io.goal_op,
camp.name AS Campaign,
io.goal_value

FROM agg_delivery_daily agg,bam_ios io,bam_roll_items roll,bam_campaigns camp

WHERE io.id=agg.io_id
AND roll.id=agg.io_roll_item_id
AND camp.id=agg.campaign_id
AND agg.advertiser_id IN ('111','123','3333')
AND agg.date='2011-01-01'
AND agg.roll_item_id=421111
--GROUP BY io.name,agg.io_roll_item_id,roll.budget,camp.name
ORDER BY io.name,agg.date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources