COUNT MAX CASE and GROUP BY for recurring type id

Question

I have this view, resulting from a multiple join:

project_id  | document_type_id
10          | 2
10          | 2
10          | 3
10          | 1
10          | 1
10          | 1
11          | 2
11          | 2
11          | 2
11          | 2
11          | 3
11          | 3

label for 1: "review"

label for 2: "interview"

label for 3: "romance"

I already obtained this table:

project_id  | review     | interview | romance
10          | OK         | OK        | OK
11          | NO         | OK        | OK

using the following query statement:

SELECT `project_id`, `document_type_id`,
MAX(CASE WHEN `document_type_id` = 1 THEN "OK" ELSE "NO" END) as "review",
MAX(CASE WHEN `document_type_id` = 2 THEN "OK" ELSE "NO" END) as "interview",
MAX(CASE WHEN `document_type_id` = 3 THEN "OK" ELSE "NO" END) as "romance"

FROM projectDocumentList
GROUP BY project_id

What I need now is to count every document type in each field in a table like that:

project_id  | review | interview | romance
10          | 3      | 2         | 1
11          | 0      | 4         | 2

I tried and tried but I cannot find the correct sintax to obtain this result. Some of my previous attempts...

COUNT(MAX(CASE WHEN `document_type_id` = 1 THEN "1" ELSE "NO" END)) as "review"

MAX(CASE WHEN `document_type_id` = 1 THEN (SELECT COUNT(`document_type_id`)) ELSE "NO" END) as "review"

COUNT(DISTINCT(MAX(CASE WHEN `document_type_id` = 1 THEN `document_type_id` ELSE "NO" END)) as "review"

Possible duplicate of [Count with if condition in mysql query](https://stackoverflow.com/questions/9798937/count-with-if-condition-in-mysql-query) — splash58, Feb 02 '19 at 16:35

Raymond Nijland · Accepted Answer · 2019-02-02T16:08:37.250

You can use SUM()

Query

SELECT `project_id`,
SUM(CASE WHEN `document_type_id` = 1 THEN 1 ELSE 0 END) as "review",
SUM(CASE WHEN `document_type_id` = 2 THEN 1 ELSE 0 END) as "interview",
SUM(CASE WHEN `document_type_id` = 3 THEN 1 ELSE 0 END) as "romance"
FROM projectDocumentList
GROUP BY project_id

Results

| project_id | review | interview | romance |
| ---------- | ------ | --------- | ------- |
| 10         | 3      | 2         | 1       |
| 11         | 0      | 4         | 2       |

demo

Or COUNT(..) can also be used but you need to use NULL instead of 0.
This is because COUNT(..) handles 0, 1 and NULL values different then SUM(..) does.

Query

SELECT `project_id`,
COUNT(CASE WHEN `document_type_id` = 1 THEN 1 ELSE NULL END) as "review",
COUNT(CASE WHEN `document_type_id` = 2 THEN 1 ELSE NULL END) as "interview",
COUNT(CASE WHEN `document_type_id` = 3 THEN 1 ELSE NULL END) as "romance"
FROM projectDocumentList
GROUP BY project_id

Results

| project_id | review | interview | romance |
| ---------- | ------ | --------- | ------- |
| 10         | 3      | 2         | 1       |
| 11         | 0      | 4         | 2       |

demo

COUNT MAX CASE and GROUP BY for recurring type id

1 Answers1