Background

I have a custom content type "Change Request" that has an "Urgency" field which is a list with three options to choose from:

• Low (3 or more days)
• Medium (Less than 3 days)
• High (Emergency)

I created four change requests with sample data and tried to create a view with an unformatted list that shows how many change requests there are with each selection.

When I try to create this view, the result shows duplicate entries with a count of 1 instead of grouping the duplicate results and showing their combined count.

Current Result (Incorrect)

Low (3 or more days)
1
High (Emergency)
1
Medium (Less than 3 days)
1
Low (3 or more days)
1

Desired Result

High (Emergency)
1
Medium (Less than 3 days)
1
Low (3 or more days)
2

Goal

My end goal is to use the result to create Charts using the Charts module. The docs for this module recommend setting the aggregator up with an unformatted list or table and then switching the format to Charts.

Setup

1. Created a view showing the content type Change request with the format Unformatted list of fields
2. Added Urgency field to fields twice (once for labels, again for data)
3. Enabled use aggregation
4. For the first Urgency field, I used the aggregation settings Group Results Together and set the column to Value
5. For the second Urgency field, I used the aggregation settings Count

Attempted Solutions

1. Searched for the solution on the Module Issues page and Drupal Stack Exchange. I found some solutions where the problem was similar, but none of them worked for me.
2. Several different modules including Field Count Formatter and Views Aggregator Plus. The first one only counted how many options were selected for the field in each change request (always resulted in 1), and the second one was not compatible with the Charts module so it was not an option.
3. Tried multiple variations for the aggregator settings for each field unsuccessfully.
4. Attempted to use the Table format with the same duplicated results. Also attempted to do this directly in the Charts module, once again with the same results.
5. Found a SQL query that does what I want but was unsuccessful in replicating this using the Views module.

Working SQL Query

I successfully used GROUP BY and COUNT with the below query:

SELECT field_urgency_value, COUNT(field_urgency_value) 
FROM node__field_urgency
GROUP BY field_urgency_value;

This results in:

+---------------------------+-------+
| field_urgency_value       | count |  
+---------------------------+-------+
| Low (3 or more days)      | 2     | 
+---------------------------+-------+
| Medium (Less than 3 days) | 1     | 
+---------------------------+-------+
| High (Emergency)          | 1     | 
+---------------------------+-------+

However I was unable to replicate these results in Views.