MySQL - query to return NULL

Question

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.

Click on the link after the word edited, and you'll see the edit history. I first changed the syntax highlighting, and then the tags. This has nothing to do with php, and selecting the sql tag will ensure sql highlighting by default, for answers as well. Plus give you more suitable experts to look at this question. — MvG
– MvG, Commented Jul 24, 2012 at 12:13
@MvG - cheers, much appreciated. Like the second option as well. — Homer_J
– Homer_J, Commented Jul 24, 2012 at 12:23

MvG · Accepted Answer · 2012-07-24 11:45:21Z

3

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.

edited Jul 24, 2012 at 11:45

answered Jul 24, 2012 at 11:21

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Homer_J Over a year ago

A very elegant solution :-) - Thank you.

Community · Accepted Answer · 2017-05-23 10:09:38Z

2

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.

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered Jul 24, 2012 at 12:04

MvG

61.8k25 gold badges158 silver badges294 bronze badges

3 Comments

Fluffeh Over a year ago

I think that this is actually much more elegant than the query that I scrawled out. +1

Homer_J Over a year ago

Yep, even nicer! :-) However, already implemented the first. Would you like me to tick this one instead???

MvG Over a year ago

@Homer_J, seeing as I believe the answer you accepted to be more general than this one here, I'd prefer the tick mark to remain where it is.

Fluffeh · Accepted Answer · 2012-07-24 11:56:27Z

0

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.

edited Jul 24, 2012 at 11:56

answered Jul 24, 2012 at 10:50

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

14 Comments

rlemon Over a year ago

Cool... a query... anything else?

Homer_J Over a year ago

@fluffeh - cheers for that - think there was an error, should it be 'null'? When I changed that I get the message 'Operand should contain 1 column(s)'

MvG Over a year ago

I entered a dummy table in sqlfiddle to check my own answer. Feel free to give yours a try there as well. Currently it does not work.

Fluffeh Over a year ago

It seems that the group by was wrong in the second coalesce. I have updated it (my own test table was working with a modified query). I have saved another sqlFiddle with the query I have updated this answer with.

MvG Over a year ago

Have a look at this fiddle. You compute the second column as 3.4, which is the result of all five rows matching the original WHERE clause. The correct result, however, would be 5.5, computed from the two rows with q25=2. In original query, the GROUP BY imposes an added condition that the AVG is computed only within each group. That condition is lacking in your second subquery.

|

Collectives™ on Stack Overflow

MySQL - query to return NULL

3 Answers 3

1 Comment

3 Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related