I have a table 'attempts'attempts like this with hundreds or records. It records some transactions. The name of transaction is important, and the error tells that transaction was not successful.
| id | name |error| data |
|----|-------|-----|------|
| 1| sara | 0 | bla |
| 2| sara | 1 | bla |
| 3| sara | 0 | bla |
| 4| john | 1 | bla |
| 5| paul | 0 | bla |
| 6| paul | 0 | bla |
| 7| john | 0 | bla |
What I want to do it to get the error and success rate by grouping them by 'name'. So for example, for name 'Sara' I have three transactions two are successful (because error is 0 (false)) and 1 unsuccessful (because error is 1 (true)). I want to get in percentage the success or error rate . The success rate is 2 out of 3 The error rate is 1 out of 3
- The success rate is 2 out of 3
- The error rate is 1 out of 3
I a bit confused cause I don't know exactly how to do it:
SELECT COUNT(id) AS 'count', name, error
FROM attempts
GROUP BY name, error
ORDER BY name
this givesThe query returns a result set like this:
|count| name |error|
|----|-------|-----|
| 2| sara | 0 |
| 1| sara | 1 |
| 1| john | 1 |
| 1| john | 0 |
| 2| paul | 0 |
but I don't know how can I calculate the rate , I want to have something like this
|count| name |error|success|
|----|-------|-----|-------|
| 3| sara | 1 | 2 |
| 2| john | 1 | 1 |
| 1| paul | 0 | 1 |
Can someone help me please?
