Re: [PHP-DB] NULL to 0 result
On 14 December 2010 21:53, Ron Piggott ron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron Doesn't that query give you an error saying that `reference` isn't in the GROUP BY clause? I use MS SQL, so the wording my be different but it would be along the lines of ... For the SQL statement : SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status Column 'bv-cluster-sql.contracts.dbo.pop_header.POH_CONTRACT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. So, fixing the query ... SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status, POH_Contract now works. So, your query may need to be SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include`, `reference` Normally COUNT() will count NULLs, but as you are grouping by the counted column, nulls would be in their own row. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0 result
On 15/12/10 21:42, Richard Quadling wrote: On 14 December 2010 21:53, Ron Piggottron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron Doesn't that query give you an error saying that `reference` isn't in the GROUP BY clause? Nah mysql lets you do it. Other db's enforce it as you pointed out. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] NULL to 0 result
What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] NULL to 0 result
On Tue, Dec 14, 2010 at 4:53 PM, Ron Piggott ron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info SELECT `reference`, ifnull(COUNT(`reference`),0) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
