0

I'm new on mysql and I don't know the terms, so I'll speak as I can. Here my problem:

I have works table, it have columns: id, date, and done.

+----+---------------------+------+
| id |        date         | done |
+----+---------------------+------+
|  1 | 2020-05-01 14:22:34 |   10 |
|  2 | 2020-05-02 14:22:45 |   50 |
|  3 | 2020-05-03 14:23:00 |   30 |
|  4 | 2020-05-04 14:23:13 |  100 |
|  5 | 2020-05-05 14:23:24 |   25 |
+----+---------------------+------+

I want to select all of them with +1 new column, named cummulative_done. I use this sql command to get the result I want.

SET @cdone := 0;
SELECT *, (@cdone := @cdone + done) as cummulative_done
FROM works;

Result:

+----+---------------------+------+------------------+
| id |        date         | done | cummulative_done |
+----+---------------------+------+------------------+
|  1 | 2020-05-01 14:22:34 |   10 |               10 |
|  2 | 2020-05-02 14:22:45 |   50 |               60 |
|  3 | 2020-05-03 14:23:00 |   30 |               90 |
|  4 | 2020-05-04 14:23:13 |  100 |              190 |
|  5 | 2020-05-05 14:23:24 |   25 |              215 |
+----+---------------------+------+------------------+

Then, I want that result to be filtered by cummulative_done using sql WHERE. On my first try, I use this sql command.

SET @cdone := 0;
SELECT *, (@cdone := @cdone + done) as cummulative_done
FROM works
WHERE cummulative_done <= 100;

And it give me error: Error in query (1054): Unknown column 'cummulative_done' in 'where clause'.

After that, I searched on google and got a solution using this sql command.

SET @cdone := 0;
SELECT *, (@cdone := @cdone + done) as cummulative_done
FROM works
WHERE @cdone + done <= 100;

It result:

+----+---------------------+------+------------------+
| id |        date         | done | cummulative_done |
+----+---------------------+------+------------------+
|  1 | 2020-05-01 14:22:34 |   10 |               10 |
|  2 | 2020-05-02 14:22:45 |   50 |               60 |
|  3 | 2020-05-03 14:23:00 |   30 |               90 |
+----+---------------------+------+------------------+

It give me the result I want, but I don't understand why it works and why mine not works.

From that, I realized my understanding of sql SELECT and WHERE is wrong. Can anyone explain to me how sql SELECT and WHERE works generally? After that please explain why my first try not works and why the solution works.

If can, I prefer step-by-step or deep explaination but still easy to understand. Thanks.

HikkiBocchi
  • 45
  • 6
  • Try searching for sql order of operations and mysql order of operations for an insight. for example https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/ – P.Salmon May 10 '20 at 07:20

1 Answers1

2

Check out the order of MySQL statements execution. WHERE is executed before SELECT so you can't filter by columns defined later.

More information: MySQL query / clause execution order

Every iteration of next row variable @cdone increases with value done from previous row so that condition applies to results. For each row order of WHERE/SELECT execution stays the same, but execution is performed for each row one by one.

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

You can modify your code.

SET @cdone := 0;
SELECT *, @cdone as cummulative_done
FROM works
WHERE (@cdone := @cdone + done) <= 100;

https://dev.mysql.com/doc/refman/8.0/en/select.html

Jsowa
  • 9,104
  • 5
  • 56
  • 60
  • Okay, I understand why my first try not work because WHERE is executed before SELECT. Then, why `@cdone` got incremented automatically on WHERE? Isn't the syntax for automatic increment exist on SELECT in `@cdone := @cdone + done`. I am still confuse about this. – HikkiBocchi May 10 '20 at 09:55
  • Every next row value of `@cdone` increases with `done` in `SELECT` so the condition in `WHERE` changes also. – Jsowa May 10 '20 at 10:04
  • Umm... Didn't you said `WHERE first`, `then SELECT`? Then you said, 'Every next row value of @cdone increases with done in SELECT so the condition in WHERE changes also.' Why? Why `WHERE also changes` when `WHERE is the first` to execute? – HikkiBocchi May 10 '20 at 10:51
  • I modified my answer. For each row order of WHERE/SELECT execution stays the same, but execution is performed for each row one by one. So it executes WHERE for first row, then SELECT for first row, then WHERE for second row, and SELECT for second row etc (this is simplified explanation, I skipped other operations). – Jsowa May 10 '20 at 11:36