Min/least function layering in question

[michaela18]

I have the following query:

SELECT

io.name AS 'IO Name' ,
IF(budget.pricing_convention LIKE '%ABC%', ROUND((IFNULL((budget.budget/(budget.unit_price)),budget.bonus_units))
- SUM(VWZ.impressions),0),
ROUND((IFNULL((budget.Goal/(budget.unit_price/1000)),budget.bonus_units))
- SUM(VWZ.impressions),0)) AS 'Left to Run',
IFNULL(SUM( CASE WHEN VWZ.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
VWZ.impressions
END ),0) AS 'volume_Last_7'


FROM VWZ_delivery_daily VWZ, uas_io_budget_items budget, uas_ios io, uas_campaigns camp, uas_users USER
WHERE VWZ.io_id = budget.io_id
AND VWZ.io_id = io.id
AND VWZ.io_budget_item_id = budget.id
AND VWZ.campaign_id = camp.id
AND VWZ.phone_unit_id NOT IN (1,2,3)
AND io.goal_type LIKE '%Moto%'
GROUP BY VWZ.io_id, VWZ.io_budget_item_id
ORDER BY io.name, budget.id


This query applies to 99% of my campaigns but it is not for 1%. The 1% that does not apply are special campaigns where volume is not counted the same. The volumn in the database is recorded twice per phone unit id per day. So the database will show:

date io_id phone_unit_id Volume
4/1/2011 12134 20 1500
4/1/2011 12134 20 1600
4/1/2011 12134 30 300
4/1/2011 12134 30 305
4/1/2011 12134 40 1000
4/1/2011 12134 40 1004




so the results will add up to 5709 for 04/01/2011. But What it should add up to is to take the smaller column of each phone unit id for that day. So for unit 20, it would be 1500 + 300 for unit 30 + 1000 for unit 40. How can I layer that into the Left to Run and Volume last 7?

 

[gmmastros]

Are you using MySQL for your database? If so, I would recommend you post your question in a forum specifically devoted to MySQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom