0

I am using this query to return return a list of songs stored in $sTable along with a COUNT of their total projects which are stored in $sTable2.

 /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."

    FROM $sTable b 
    LEFT JOIN (
   SELECT COUNT(*) AS projects_count, a.songs_id

   FROM $sTable2 a
   GROUP BY a.songs_id
) bb ON bb.songs_id = b.songsID


        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

'projects_count' is put into an array along with the columns in '$sTable', this is then spat out via JSON and displayed in a table on page.

This is working perfectly apart from when a song has no projects linked to it. It of course returns NULL.

All I want is for any null values to be returned as a '0'.

I have tried the COUNT(), COUNT(IFNULL (project_id,0) and using COUNT(DISTINCT)...

And also:-

SELECT COALESCE(COUNT(*),0) AS projects_count, a.songs_id

All without success.

Any ideas?

gordyr
  • 6,078
  • 14
  • 65
  • 123

5 Answers5

5

Use the COALESCE() function. COALESCE() takes at least 2 arguments, calculated in order, and returns the first non-null argument. So COALESCE(null, 0) would return 0, and COALESCE(null, null, null, null, 1) would return 1. Here's MySQL's documentation about COALESCE().

In re-reading your query, you should be able to get the results you want like this:

SELECT <all the fields you want>, b.songsID, COUNT(*) AS projects_count
FROM $sTable b
LEFT OUTER JOIN $sTable2 bb ON bb.songs_id = b.songsID
$sWhere
GROUP BY b.songsID
$sOrder
$sLimit

Like I said, this should work, but something about it doesn't feel quite right.

WWW
  • 9,734
  • 1
  • 29
  • 33
  • Thanks to all who have answered, however none of the suggestions seem to be giving me anything other than 'null' including using COALESCE. That said however, COALESCE is new to me and I am uncertain on the appropriate syntax to use when combining it with COUNT(*). If you could perhaps elaborate with my code example I would be most grateful as my attempts seem to still result in 'null'. Thanks! – gordyr Nov 01 '11 at 18:32
  • Sadly it still doesn't, although I really appreciate the effort. As much as I hate to I may have to temporarily put a jquery hack in place to replace the null values with 0 until I can find a solution. :-( – gordyr Nov 01 '11 at 21:32
2

COALESCE() returns the first non-null argument. So if you say COALESCE(count(...),0) it will return the count(...) if it's not null, or it will return 0 if the count(...) is null

Дамян Станчев
  • 2,654
  • 5
  • 30
  • 53
1

You don't have to do the join with a subquery. The following should work just fine without the COALESCE etc:

SELECT ".str_replace(" , ", " ", implode(", ", $aColumns)).", 
SUM(b.songsID is not null) as countprojects
FROM $sTable b 
LEFT JOIN $sTable2 a ON a.songs_id=b.songsID
GROUP BY ".str_replace(" , ", " ", implode(", ", $aColumns))."

This will return what you ask for in countprojects.

The way it works: The LEFT JOIN just makes certain you get all data. You can't use COUNT because that would return 1 for the NULL rows. But, if you just use the fact that a boolean TRUE evaluates to 1, and a boolean FALSE evaluates to 0, you can SUM over those results.

Eljakim
  • 6,877
  • 2
  • 16
  • 16
  • Although this looks like a much better solution it isn't working for me, and my inexperience with MySQL leaves me unable to adapt your example code to work properly with the rest of mine. I have updated my question to give more detail. Huge thanks for the suggestion though, I think we are certainly getting closer. – gordyr Nov 01 '11 at 19:16
  • Would you mind printing the result of my query and pasting that? – Eljakim Nov 01 '11 at 19:37
  • It throws back a JSON formatting error for me... within my app. I'm not sure how useful the actual query results will be but they are: .str_replace(;" implode(";" $aColumns)).";"5059" If this is not what you were after I sincerely apologise, as stated i'm extremely new to MySQL and the methods in which one would debug queries. If would would help I can paste the whole PHP file (it's not that long) that I am using to perform the queries and return the JSON? – gordyr Nov 01 '11 at 19:47
  • To clarify the above, your query returns only 1 result. Whereas my original one returns all 5000 entries. – gordyr Nov 01 '11 at 19:55
0

Simply add this line in your code after SELECT

IF(projects_count IS NULL, 0, projects_count) As projects_countList

Like This: 

$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).",
     IF(projects_countIS NULL, 0, projects_count) As projects_countList

FROM $sTable b 
LEFT JOIN (SELECT COUNT(*) AS projects_count, a.songs_id FROM $sTable2 a GROUP BY a.songs_id) bb ON bb.songs_id = b.songsID
    $sWhere
    $sOrder
    $sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

To return 0 instead of null in MySQL
USE

SELECT id, IF(age IS NULL, 0, age) FROM tblUser

USE with count() having join 2 tables
like 

SELECT 
    tblA.tblA_Id,
    tblA.Name,
    tblC.SongCount,
    IF(tblC.SongCount IS NULL, 0, tblC.SongCount) As noOfSong
  FROM  tblA
    LEFT JOIN
    (   
        SELECT 
            ArtistId,count(*) AS SongCount 
        FROM 
            tblB  
        GROUP BY 
            ArtistId
    ) AS tblC
    ON 
        tblA.tblA_Id = NoOfSong.ArtistId

And Result is 

tblA_Id    Name     SongCount   noOfSong
-----------------------------------------------------
7          HSP      NULL        0
6          MANI     NULL        0
5          MEET     1           1
4          user     NULL        0
3          jaani    2           2
2          ammy     NULL        0
1          neha     2           2
HSP
  • 345
  • 4
  • 4
0
SELECT blahblahblah, IFNULL(bb.projects_count, 0)
FROM $sTable b
etc...
Marc B
  • 356,200
  • 43
  • 426
  • 500