How can I create a view showing count of users by birth year?

Question

I want to create a table showing a count of number of users by birth year. Right now I've done the following:

1. Create a view of profiles rendered as a table (no grouping fields, though I've tried grouping to no avail)
2. Create two fields:
   1. Birth date, rendered as four digit year, aggregated as "Group results together"
   2. Profile ID, aggregated as COUNT DISTINCT
3. Sorted these by descending birth date

The problem is that this does not group the dates by year - it still groups the dates by day. Right now, rather than one line in my table for each year, I'm getting something like this:

2017 | 1
2017 | 2
2017 | 1
2017 | 1
2017 | 3
2017 | 1

...the left column being the four digit year and the right column being the number of users born on a particular day in 2017. The above should have rendered as:

2017 | 9

How can I get my table to show a count of users by birth year?

Answer 1

You could try out the following module which enables aggregation on your chosen granularity: https://www.drupal.org/project/views_date_format_sql

After enabling, a checkbox will appear in the Date field setting of the view, called 'Use SQL to format date'.