Problem/Motivation
Hi everyone, I am trying to aggregate data from some fields using the Views core module into an unformatted list so that I can later generate charts using this Charts module.
The problem I am experiencing is that when I try to aggregate the fields, the labels are being duplicated instead of getting grouped, and the duplicates are being counted separately. This results in the charts creating duplicates instead of grouping the data.
Low (3 or more days)
1
High (Emergency)
1
Medium (Less than 3 days)
1
Low (3 or more days)
1
The desired result is this:
High (Emergency)
1
Medium (Less than 3 days)
1
Low (3 or more days)
2
I included a screenshot of the resulting chart.
Setup
I have a site where users can submit change requests. I created a custom content type "Change Request" which has numerous fields in it. One of these fields is urgency which is a list containing three options (Low, Medium and High). My goal is to create a pie chart with these three options and the count of change requests for each urgency.
- Created a view showing the content type Change request with the format Unformatted list of fields
- Added Urgency field to fields twice (once for labels, again for data)
- Enabled use aggregation
- For the first Urgency field, I used the aggregation settings Group Results Together and set the column to Value
- For the second Urgency field, I used the aggregation settings Count
Attempted Solutions
- Searched the Drupal Docs and the forums for solutions. I attempted multiple solutions to no avail.
- Attempted to use various aggregation modules.
- Attempted different aggregation settings.
- Attempted using chart and table instead of an unformatted list, but each option had the same incorrect result.
- Asked multiple questions on Stack Exchange.
- Attempted a successful SQL approach, but unable to recreate it using Views aggregation.
SQL Query
I found a SQL query that generates what I need essentially, I just don't know how to implement this using the aggregate settings:
SELECT field_urgency_value, COUNT(field_urgency_value) FROM node__field_urgency
GROUP BY field_urgency_value;
Which results in:
+---------------------------+-------+
| field_urgency_value | count |
+---------------------------+-------+
| Low (3 or more days) | 2 |
+---------------------------+-------+
| Medium (Less than 3 days) | 1 |
+---------------------------+-------+
| High (Emergency) | 1 |
+---------------------------+-------+
Additional Info
I understand that this isn't a problem with the Charts module and is due to an issue in my field/aggregation settings. I have asked this question on the Drupal Stack Exchange but I haven't seen any replies yet. I am asking the question here as there may be users here who have experienced this issue before.
I am happy to provide more info if needed.