CODEWITHSUNDEEP
mysqlsqlwhere-clause
Suhail Gupta
Suhail Gupta

Reputation: 23216

How to get an empty set if a particular condition is not met

The following query works fine but the problem is, it always returns a result. It never returns an empty set.

SELECT sum(vmin_consumed) AS vmin
FROM usageinfo
WHERE job_end_time BETWEEN '2017-03-14' AND '2017-03-30'
AND username='tytyjt'

If any condition is not met, it returns NULL. Could I make the above query return an empty result set if the second condition (username='foo') is not met?

Upvotes: 1

Views: 109

Answers (4)

Rahul Patel
Rahul Patel

Reputation: 639

Try this:

SELECT p.* FROM (SELECT sum(vmin_consumed) AS vmin, username
FROM usageinfo
WHERE job_end_time BETWEEN '2017-03-14' AND '2017-03-30' AND username = "foo") p where p.username = "foo"

Upvotes: 1

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Try this:

SELECT _TABLE.VMIN FROM (SELECT SUM(vmin_consumed) AS VMIN
FROM usageinfo
WHERE job_end_time BETWEEN '2017-03-14' AND '2017-03-30' AND _TABLE.username =   'tytyjt') 
_TABLE
WHERE _TABLE.VMIN IS NOT NULL

Upvotes: 0

Blank
Blank

Reputation: 12378

This is really a strange requirement, try this:

SELECT
    SUM(
        CASE WHEN job_end_time BETWEEN '2017-03-14' AND '2017-03-30' THEN vmin_consumed -- The first condition is here.
             ELSE null 
        END) AS vmin
FROM usageinfo
GROUP BY username
HAVING username = 'tytyjt' -- The second condition is here.

I have to admit this will cause performance issue when you have big data.

Upvotes: 0

HarisH Sharma
HarisH Sharma

Reputation: 1247

You need this,

SELECT IFNULL(SUM(vmin_consumed), '') AS vmin
FROM usageinfo
WHERE job_end_time BETWEEN '2017-03-14' AND '2017-03-30'
AND username='tytyjt'

Upvotes: 0

Related Questions