Re: SUM() Math in mySQL
| From: | Ron Piggott | Date: | Mon, 20 Dec 2010 08:34:42 +0000 |
| Subject: | Re: SUM() Math in mySQL | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-47564@lists.php.net to get a copy of this message | ||
Thanks Chris
I tweaked the query and database a bit so I only do 1 query and use PHP with the search results
twice:
- initially to calculate the number of records which are in the RSS Feed already
- if that number is less than 15 I make the new rss_feed.xml file using the same search results
because the SELECT queried for the information I am using for this RSS Feed.
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
From: Ron Piggott
Sent: Sunday, December 19, 2010 11:38 PM
To: php-db@lists.php.net
Subject: SUM() Math in mySQL
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