Lets say that i have this table in mariadb:
Table1:
| Col1 | Col2 | Col3 |
|---|---|---|
| Test1 | Done | 01-08-2021 |
| Test2 | Done | 01-08-2021 |
| Test3 | Waiting | 02-08-2021 |
| Test4 | Done | 01-08-2021 |
| Test5 | Fail | 01-08-2021 |
| Test6 | Done with errors | 01-08-2021 |
| Test7 | Finished | 03-09-2021 |
| Test8 | Failed with many errors | 10-08-2021 |
| Test9 | Not tested yet | 10-10-2021 |
How can i make an sql query so i can have an output like this:
Table2
| Col1 | Col2 |
|---|---|
| Finished | 5 |
| Waiting to be finished | 2 |
| Failed | 2 |
So baically, what i want is to count the numbers of rows in Table1 which contain: Done, Done with errows or Finshed and to write that number in row Finished in Table2. For the rows in Table1 that contain: Fail and Failed with many errors to be written to row Failed in Table2. For the rows in Table1 that contain Waiting and Not tested yet to be written to row Waiting to be finished in Table2.
CodePudding user response：
You may aggregate using a CASE expression:
SELECT
CASE WHEN Col2 IN ('Done', 'Done with errors', 'Finished') THEN 'Finished'
WHEN Col2 LIKE '