Nested select question

Databases
1

I have the following query:

SELECT

SA.name AS ‘Name’ , budget.id as ‘budget id’

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, AAC_campaigns camp, AAC_users USER
WHERE VWZ.io_id = budget.io_id
AND VWZ.io_id = SA.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)
GROUP BY VWZ.io_id, VWZ.io_budget_item_id
ORDER BY SA.name, budget.id

This query just adds up the volume of the last 7 days.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 volume in the database is recorded twice per phone unit id per day. So the database is like:

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?

I want to say if SA.Type like ‘%Double%’ then I want to take the lesser of the volume for that day for that phone unit id in my queries.

Something like

IF sa.type like ‘%double%’, (select min(sum(vwz.impressions)…

(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’