minimum value from a table using mysql

Question

I am looking to find the day of the week and time of the day in which a shopping center carpark is least occupied. I need to find the minimum value of multiple columns. So far I have been able to find the minimum value of each time column, but cannot find the minimum off all the times as a group.

Below is a small example of my table, the actual table is much larger. The percentages represent how occupied the shopping center carpark is at the respected time.

Example table: shopping_centre_carpark_occupancy;

Name | Day       | 4pm | 5pm | 6pm | 7pm 
     |           |     |     |     |
x    | Monday    |74%  | 55% | 25% | 15% 
y    | Tuesday   |87%  | 67% | 72% | 10%
z    | Wednesday |74%  | 80% | 85% | 76%

*The table structure cannot be changed

I need to find out what day of the week and time the carpark is least occupied. How can I find the minimum occupancy?

Thank you

Use `LEAST(4pm, 5pm, 6pm, 7pm)` to find the lowest occupancy on a day. Then use one of the techniques in http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 to find the row where that's lowest. It would be easier if you just had a single `time` column instead of separate columns for each hour. — Barmar, Sep 27 '16 at 21:06

score 0 · Answer 1 · answered Sep 27 '16 at 20:24

Does it have to be done in SQL? I'm sure there is a way, but it would probably be a lot easier in code. I would just select all from the table. Then on the first loop through the data, store the date, time, and % in variables. Then for the subsequent records, compare the new % to the % you stored in a variable. If it is lower, overwrite your variables with the new data. When you get to the end of the dataset, you should have the day and time with the lowest %.

score 0 · Answer 2 · answered Sep 27 '16 at 20:30

0

You can do it with a UNION of a lot of subqueries, but it will be ugly:

SELECT MIN(v) FROM (
    SELECT MIN(4pm) v FROM table
    UNION
    SELECT MIN(5pm) v FROM table
    UNION
    SELECT MIN(5pm) v FROM table
    ...
)

answered Sep 27 '16 at 20:30

Tordek

10,628
3
36
67

minimum value from a table using mysql

2 Answers2