Don't want negative result of subtraction in MySQL

user_15204956 0
Viewed 121

So I have the following code

CREATE TABLE dispenser(
    id_disp int not null auto_increment,
    location_disp varchar(20) not null,
    full_capacity int not null,
    primary key (id_disp)
);


CREATE TABLE records(
    time_stamp  DATETIME DEFAULT CURRENT_TIMESTAMP not null,
    id_dispenser int not null,
    nr_pumps int not null,
    primary key (id_dispenser,time_stamp)
);

CREATE VIEW left_capacity AS
    SELECT 
        max(time_stamp) AS 'calendar', 
        id_dispenser AS 'dispenser', 
        full_capacity AS 'capacity', 
        (full_capacity-(nr_pumps*3)) AS 'available'
    FROM records r, dispenser d
    WHERE r.id_dispenser=d.id_disp
    GROUP by id_dispenser
    ORDER by id_dispenser desc;

and I have a problem on my view table where it says: (full_capacity-(nr_pumps*3)) because if nr_pumps*3 is bigger than full_capacity, I will have a negative available value. Is there a way I can make the value 0 if the calculation result is negative?

mysql sql
1 Answers

You can use greatest(). It returns the greatest of its arguments.

CREATE VIEW left_capacity
AS
SELECT max(time_stamp) AS calendar,
       id_dispenser AS dispenser,
       full_capacity AS capacity,
       greatest(full_capacity - nr_pumps * 3, 0) AS available
       FROM records r
            INNER JOIN dispenser d
                       ON d.id_disp = r.id_dispenser
       GROUP by id_dispenser;

Some additional notes:

  • Do not enclose identifiers, such as column aliases in single quotes. Singles quotes in SQL are meant for string or date literals. MySQL might accept aliases in single quotes but other DBMS do not. So don't even start getting used to it.
  • It is recommended to use the explicit JOIN syntax over the old comma separated list in the FROM clause. The former is easier to write without errors, to read and to understand.
  • Your use of GROUP BY makes the query malformed. Not every column in the list of selected columns is either an argument to an aggregation function, in the GROUP BY clause or fully functional dependent of a column in the GROUP BY clause. Sadly older versions of MySQL or badly configured ones won't enforce that rule. But the results can be funny, so prepare to be in for a surprise one day.
  • I don't know if MySQL might be an exception here. But in general an ORDER BY in a view is pointless (unless LIMIT is involved too). Unless the outermost query explicitly defines an order with an ORDER BY, SQL engines generally are allowed to return results in any order and will do if deemed necessary. So the ORDER BY in a view might get optimized away.
user_96614240
Related