NULL to 0 result

------=_NextPart_000_0000_01CB9BAF.731D6740
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable


What change is needed to this query so if =
â€=9Ccurrently_in_rssâ€=9D 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` =3D 1 GROUP BY =
`rss_feed_include`

Ron

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

------=_NextPart_000_0000_01CB9BAF.731D6740--

Re: NULL to 0 result

On Tue, Dec 14, 2010 at 4:53 PM, Ron Piggott
wrote:
>
> What change is needed to this query so if =93currently_in_rss=94 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` =3D 1 GROUP BY `rss_feed_include`
>
> Ron
>
> The Verse of the Day
> =93Encouragement from God=92s Word=94
> http://www.TheVerseOfTheDay.info
>

SELECT `reference`, ifnull(COUNT(`reference`),0) AS currently_in_rss
FROM `ministry_profiles` WHERE `rss_feed_include` =3D 1 GROUP BY
`rss_feed_include`

--=20

Bastien

Cat, the other other white meat

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

Re: NULL to 0 result

On 14 December 2010 21:53, Ron Piggott wro=
te:
>
> What change is needed to this query so if â€=9Ccurrently_in_rssâ€=
=9D 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` =3D 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 =3D 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 =3D 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` =3D 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.

--=20
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: NULL to 0 result

On 15/12/10 21:42, Richard Quadling wrote:
> On 14 December 2010 21:53, Ron Piggott 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