I have a table like the below
| Pet Name | Test | Result |
|---|---|---|
| Celine | A | Positive |
| Diogo | A | Negative |
| Jordah | A | Positive |
| Diogo | B | Positive |
| Jordah | B | Negative |
| Caesar | A | Negative |
I need to come up with a query (or a view) showing the below
| Pet Name | Total tests | Positive |
|---|---|---|
| Diogo | 2 | 1 |
| Jordah | 2 | 1 |
| Celine | 1 | 1 |
| Caesar | 1 | 0 |
I tried a query like
SELECT Pet Name, COUNT(*)
WHERE Result=‘Positive’
GROUP BY Pet Name
ORDER BY COUNT(*) DESC
which is ok, but it doesn’t provide me with the number of tests done by each pet.
The CASE WHEN sort of options do not seem to be applicable as the table gets populated with more Pet Names as vet makes new friends.
Thanks in advance.
This has been identified as duplicate of this, but I don't think it's the case. As I mentioned in above, it's the actual query that will provide the pet name, I won't know that name in advance, so no conditions such as IF or CASE WHEN sounds like helpful.
