[PHP-DB] How do I do math with a UNION ALL

2011-04-16 Thread Ron Piggott 

Hi Everyone.

I am trying to do a SUM of the impressions and usage columns from 2 
different tables using a UNION ALL.


I have tested that the individual SELECT queries work.  I am unsure of the 
layout for the UNION ALL.  Would you help me tweak this query?


I also wanted to ask is usage a reserved word in mySQL?  Is this the 
reason the query fails?


Ron

===

SELECT SUM( impressions ) AS impressions, SUM ( usage ) AS usage FROM (

(

SELECT IF ( SUM( `web_advertisements_our_clients_usage`.`impressions` ) , 
SUM( `web_advertisements_our_clients_usage`.`impressions` ) , 0 ) AS 
impressions, IF ( SUM( `web_advertisements_our_clients_usage`.`usage` ) , 
SUM( `web_advertisements_our_clients_usage`.`usage` ) , 0 ) AS usage FROM 
`web_advertisements_our_clients_usage` WHERE 
`web_advertisements_our_clients_reference` =  1 AND `month` = 4 AND `year` = 
2011 LIMIT 1


) UNION ALL (

SELECT `impressions`, `usage` FROM `web_advertisements_our_clients` WHERE 
`reference` = 1 LIMIT 1


)

) AS monthly_stats_total;


The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: RE：[PHP-DB] How do I do math with a UNION ALL

2011-04-16 Thread Ron Piggott 

Thank you for the suggestion.  This is the actual query that I implemented:  
Amazing how this works.  Ron

SELECT SUM( `impressions_total` ) AS impressions_total , SUM( `usage_total` ) 
AS usage_total FROM (

(

SELECT IF ( SUM( `web_advertisements_our_clients_usage`.`impressions` ) , SUM( 
`web_advertisements_our_clients_usage`.`impressions` ) , 0 ) AS 
impressions_total, IF ( SUM( `web_advertisements_our_clients_usage`.`usage` ) , 
SUM( `web_advertisements_our_clients_usage`.`usage` ) , 0 ) AS usage_total FROM 
`web_advertisements_our_clients_usage` WHERE 
`web_advertisements_our_clients_reference` =  
$web_advertisements_our_clients_reference AND `month` = $stats_month AND `year` 
= $stats_year LIMIT 1

) UNION ALL (

SELECT `impressions` AS impressions_total, `usage` as usage_total FROM 
`web_advertisements_our_clients` WHERE `reference` = 
$web_advertisements_our_clients_reference LIMIT 1

)

) AS monthly_stats_total;

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info