SUM() Math in mySQL
| From: | Ron Piggott | Date: | Mon, 20 Dec 2010 04:38:08 +0000 |
| Subject: | SUM() Math in mySQL | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-47562@lists.php.net to get a copy of this message | ||
I am working on the query below. It’s purpose is to manage the RSS Feed for the site.
The part of the query I am struggling with is:
SUM(
include_in_rss_feed) AS current_rss_feed
What I need is the total value of *ALL* the include_in_rss_feed , not the specific row.
include_in_rss_feed is an integer. It only ever has a value of 1
My thinking is if the total value of include_in_rss_feed and if it is less than 15 the
RSS Feed needs to be regenerated because of recent updates to the site.
Thanks in advance for helping me. This is for a clients site, not mine. I really appreciate it.
Ron
SELECT new_rss_feed_listings.reference, new_rss_feed_listings.last_update,
SUM(include_in_rss_feed) AS current_rss_feed,
ministry_profiles.organization,
ministry_profiles.city,
ministry_profiles.province_state,
ministry_profiles.postal_zip_code,
ministry_profiles.country
FROM (
(
SELECT reference , last_update
FROM ministry_profiles
WHERE live =1
ORDER BY last_update DESC
LIMIT 15
)
UNION ALL (
SELECT ministry_profiles_reference , last_update
FROM ministry_profiles_activity
WHERE live =1
GROUP BY ministry_profiles_reference
ORDER BY last_update DESC
LIMIT 15
)
UNION ALL (
SELECT ministry_profile_reference , last_update
FROM ministry_profiles_listing_details
WHERE live =1
GROUP BY ministry_profile_reference
ORDER BY last_update DESC
LIMIT 15
)
UNION ALL (
SELECT ministry_profiles_reference , last_update
FROM ministry_profile_categories
WHERE live =1
GROUP BY ministry_profiles_reference
ORDER BY last_update DESC
LIMIT 15
)
) AS new_rss_feed_listings
LEFT OUTER JOIN rss_feed_listings ON new_rss_feed_listings.reference =
rss_feed_listings.ministry_profiles_reference
INNER JOIN ministry_profiles ON ministry_profiles.reference =
new_rss_feed_listings.reference
ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info