Return multiple COUNT fields for distinct values per a unique ID based on two criteria conditions

Question

I'm trying to work with some logging data in MySQL where I want to be able to tell, per individual, counts of the number of distinct successful requests, distinct unsuccessful requests, non-distinct successful requests, and non-distinct unsuccessful requests that all meet additional global criteria.

Here is a sample of made up data that represents the original table:

+--------------+--------+---------------+----------+------+
| request_date | client |    request    | response | site |
+--------------+--------+---------------+----------+------+
| 10/30/2018   | Amy    | RE_food       |      200 | MD   |
| 10/30/2018   | Amy    | RE_food       |      200 | MD   |
| 10/30/2018   | Amy    | RE_transport  |      200 | MD   |
| 10/30/2018   | Amy    | RE_assistance |      404 | MD   |
| 10/30/2018   | Amy    | OA_assistance |      404 | MD   |
| 10/30/2018   | Bob    | RE_food       |      200 | MD   |
| 10/30/2018   | Dan    | RE_food       |      404 | MD   |
| 10/30/2018   | Dan    | RE_food       |      404 | SE   |
| 10/30/2018   | Mike   | RE_transport  |      200 | SE   |
| 10/30/2018   | Sally  | RE_food       |      404 | SE   |
| 10/30/2018   | Sally  | OA_food       |      404 | MD   |
| 10/30/2018   | Zeb    | RE_assistance |      404 | MD   |
| 10/30/2018   | Zeb    | OA_assistance |      404 | MD   |
+--------------+--------+---------------+----------+------+

I want to write a query that will return

+--------+-----------+------------+----------+-----------+
| client | unique_OK | unique_404 | total_OK | total_404 |
+--------+-----------+------------+----------+-----------+

where unique means counting only the distinct client, request, response groupings. it would return the results

+--------+-----------+------------+----------+-----------+
| client | unique_OK | unique_404 | total_OK | total_404 |
+--------+-----------+------------+----------+-----------+
| Amy    |         2 |          1 |        3 |         1 |
| Bob    |         1 |          0 |        1 |         0 |
| Dan    |         0 |          1 |        0 |         1 |
| Mike   |         0 |          0 |        0 |         0 |
| Sally  |         0 |          0 |        0 |         0 |
| Zeb    |         0 |          1 |        0 |         1 |
+--------+-----------+------------+----------+-----------+

(Note that shown above I am showing the clients with 0 counts across all four columns for clarity about the process; it's fine if they are not included in the response.)

I tried queries like this that don't work, but hopefully it makes sense as to what I'm trying to do:

SELECT client AS person, 
COUNT(DISTINCT request WHERE response = '200') AS unique_OK, 
COUNT(DISTINCT request WHERE response = '404') AS unique_404 
COUNT(WHERE response = '200') as total_OK,
COUNT(WHERE response = '404') as total_404
FROM transactions 
WHERE request like '%RE%' and site = MD and DATE_FORMAT(request_date, '%Y%m%d')='20181030';

"Don't work" will not help us help you. "Don't work" how? Wrong result? No result? Error out? — Eric, Oct 15 '19 at 19:44
@Eric I just meant to say that I knew it wasn't legal syntax. Returns syntax error without executing. — lupinist, Oct 15 '19 at 19:56
Then include the error in your question. People can't guess what's on your mind. — Eric, Oct 15 '19 at 21:31

GMB · Accepted Answer · 2019-10-15T19:53:37.147

3

Use conditional aggregation:

select
    client,
    count(distinct case when response = 200 then request end) unique_ok,
    count(distinct case when response = 404 then request end) unique_404,
    sum(response = 200) total_ok,
    sum(response = 404) total_404
from mytable
where 
    request like '%RE%'
    and site = 'MD' 
    and request_date = '2018-10-30'
group by client

Demo on DB Fiddle:

| client | unique_ok | unique_404 | total_ok | total_404 |
| ------ | --------- | ---------- | -------- | --------- |
| Amy    | 2         | 1          | 3        | 1         |
| Bob    | 1         | 0          | 1        | 0         |
| Dan    | 0         | 1          | 0        | 1         |
| Zeb    | 0         | 1          | 0        | 1         |

edited Oct 15 '19 at 19:53

answered Oct 15 '19 at 19:46

GMB

216,147
25
84
135

thanks, works in the original table as well! "then request" is a little cryptic to me and I'll have to read up on why that works. – lupinist Oct 15 '19 at 20:17
Welcome @lupinist. `case when response = 200 then request end)` returns `request` if `response = 200`, else it returns `null`. Since `count(distinct ...)` ignores `null`, this expression counts distinct values of `request` where `response = 200`. – GMB Oct 15 '19 at 20:21

Return multiple COUNT fields for distinct values per a unique ID based on two criteria conditions

1 Answers1