Re: SUM() Math in mySQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx 
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

[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux