CODEWITHSUNDEEP
mysqlsqlnull
Homer_J
Homer_J

Reputation: 3323

MySQL - query to return NULL

I have the following code:

SELECT q25, (
(
AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall
FROM t_results
WHERE brand =  'XYZ'
AND DATE =  'MAY2012'
GROUP BY q25
ORDER BY Overall 
DESC LIMIT 1

If there is no data found by the query phpmyadmin returns the following message (which is quite correct):

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0178 sec )

However, what I'd like is to actually return a NULL value, is this possible? I appreciate this might not be best practise but I'm working with inherited code and this might be the simplist and quickest route to a solution.

Thanks as always,

H.

Upvotes: 4

Views: 1975

Answers (3)

MvG
MvG

Reputation: 60968

You can use a UNION combined with a LIMIT to supply the NULL values:

(SELECT q25,
        (AVG(q1) + AVG(q2) + AVG(q3))/3 AS Overall
 FROM t_results
 WHERE brand = 'XYZ'
 AND DATE = 'MAY2012'
 GROUP BY q25
 ORDER BY Overall DESC
 LIMIT 1
)
UNION ALL
(SELECT NULL, NULL)
LIMIT 1;

This only works when you know that the first query will never yield more than one result, though. Which is the case here, so this might be the best solution for you, but the approach given in my other answer is more general.

There is a fiddle for this to experiment with.

Upvotes: 2

Fluffeh
Fluffeh

Reputation: 33532

The coalesce() function can be used to return the first non-null value from a number of comma separated columns or strings. The values/columns are evaluated left to right, so if you want to pop a string into the arguments that isn't null, make sure you place it to the right of the columns that you are testing against.

select
    coalesce(
    (
    SELECT
        q25
    FROM 
        t_results
    WHERE 
        brand =  'XYZ'
        AND DATE =  'MAY2012'
    GROUP BY 
        q25
    LIMIT 1
    ), 'null') as q25,
    coalesce(
    (
    SELECT 
        ((AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall
    FROM t_results
    WHERE 
        brand =  'XYZ'
        AND DATE =  'MAY2012'
    LIMIT 1
    ), 'null') as Overall
from 
    t_results
group by 
    1, 2;

If you don't have data that matches your where clause, this will return null, null as a row.

Upvotes: 0

MvG
MvG

Reputation: 60968

Create a table with exactly one row. Then you can use left join to achieve the desired NULL result.

CREATE TABLE dummy (d TINYINT NOT NULL);
INSERT INTO dummy SET d = 1;

SELECT q25,
       ( ( AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall
FROM dummy LEFT JOIN t_results
  ON brand = 'XYZ'
 AND DATE = 'MAY2012'
GROUP BY q25
ORDER BY Overall DESC
LIMIT 1

You can also replace the dummy table with a subquery:

SELECT q25,
       ( ( AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall
FROM (SELECT 1) AS dummy LEFT JOIN t_results
  ON brand =  'XYZ'
 AND DATE =  'MAY2012'
GROUP BY q25
ORDER BY Overall DESC
LIMIT 1

Tested this via sqlfiddle, where you can also experiment with alternatives.

The conditions selecting the result, which used to be in the WHERE clause, now have to go into the ON clause. Otherwise the left join would produce non-NULL rows which would be removed by the WHERE, instead of generating a single NULL row if no matching row could be found. If there were no WHERE conditions in the original query, ON 1 could be used to express any row matches.

Upvotes: 3

Related Questions