SUM() Math in mySQL

------=_NextPart_000_0005_01CB9FD5.CA61F960
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I am working on the query below. Itâ€=99s 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` =3D1
ORDER BY `last_update` DESC
LIMIT 15
)
UNION ALL (

SELECT `ministry_profiles_reference` , `last_update`
FROM `ministry_profiles_activity`
WHERE `live` =3D1
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` =3D1
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` =3D1
GROUP BY `ministry_profiles_reference`
ORDER BY `last_update` DESC
LIMIT 15
)
) AS new_rss_feed_listings=20
LEFT OUTER JOIN `rss_feed_listings` ON new_rss_feed_listings.reference =
=3D `rss_feed_listings`.`ministry_profiles_reference`=20
INNER JOIN `ministry_profiles` ON `ministry_profiles`.`reference` =3D =
new_rss_feed_listings.reference=20
ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info

------=_NextPart_000_0005_01CB9FD5.CA61F960--

Re: SUM() Math in mySQL

On 20/12/10 15:38, Ron Piggott wrote:
> 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

Well you're going to get an error with your current attempt - but you
know that already I assume. You have an aggregate function (sum) but no
group by clause for the outer query.

I think you're better off just having 2 queries - one with the sum() and
the group by include_in_rss_feed, and one with all of your other data. I
don't think combining them will be very easy.

--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: SUM() Math in mySQL

------=_NextPart_000_0005_01CB9FF6.D6B49080
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


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
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info


From: Ron Piggott=20
Sent: Sunday, December 19, 2010 11:38 PM
To: php-db@lists.php.net=20
Subject: SUM() Math in mySQL

I am working on the query below. Itâ€=99s 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` =3D1
ORDER BY `last_update` DESC
LIMIT 15
)
UNION ALL (

SELECT `ministry_profiles_reference` , `last_update`
FROM `ministry_profiles_activity`
WHERE `live` =3D1
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` =3D1
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` =3D1
GROUP BY `ministry_profiles_reference`
ORDER BY `last_update` DESC
LIMIT 15
)
) AS new_rss_feed_listings=20
LEFT OUTER JOIN `rss_feed_listings` ON new_rss_feed_listings.reference =
=3D `rss_feed_listings`.`ministry_profiles_reference`=20
INNER JOIN `ministry_profiles` ON `ministry_profiles`.`reference` =3D =
new_rss_feed_listings.reference=20
ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info

------=_NextPart_000_0005_01CB9FF6.D6B49080--