How do I do math with a UNION ALL
| From: | Ron Piggott | Date: | Sat, 16 Apr 2011 19:49:30 +0000 |
| Subject: | How do I do math with a UNION ALL | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-47763@lists.php.net to get a copy of this message | ||
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