Search code examples
mysqlselectnotnull

Mysql select var if not null

Some of the fields (url, poster, page) are null in my table (because this value as added later). When selecting all with following query, I will get null for these fields (url, poster, page), while the rest of the values are correct (sums). This value (url, poster, page) is the same for all with same media_id.

How could I get these values being not null (if they are set in at least 1 row) in my query? Note that I dont need WHERE IS NOT NULL here.

enter image description here

Query:

"SELECT media_id, title, poster, url, page, SUM(c_play) AS total_play, SUM(c_time) AS total_time, SUM(c_download) AS total_download FROM {$statistics_table} GROUP BY media_id, title ORDER BY $order $dir"

Thsi is just a single result example:

 "media_id": "41",
    "title": "Beach sunset",
    "poster": null,
    "url": null,
    "page": null,
    "total_play": "5",
    "total_time": "42",
    "total_download": "2",
Solution

  • I suggest using MAX function:

    SELECT 
    media_id, 
    title, 
    MAX(poster) AS poster, 
    MAX(url) AS url, 
    MAX(page) AS page, 
    SUM(c_play) AS total_play, 
    SUM(c_time) AS total_time, 
    SUM(c_download) AS total_download 
FROM 
    {$statistics_table} 
GROUP BY 
    media_id, 
    title 
ORDER BY 
    $order $dir

    This way, you ensure that you get the non-null values for url, poster, and page if they are set for any of the rows with the same media_id.