DISTINCT ON still gives me an error that select item should be in GROUP BY

Question

I have a table with a list of customer IDs and a list of dates as follows:

id |take_list_date | customer_id
1  |2016-02-17     | X00001
2  |2016-02-20     | X00002
3  |2016-02-20     | X00003

I am trying to return a count of all the IDs in the table on a specific day in the following format:

label: 2016-02-20 value: 2

The following query produces the required results within the specified date range:

select 
  count(customer_id)::int as value, 
  take_list_date::varchar as label 
FROM    
  customer_take_list 
where 
  take_list_date >= '10-12-2017' 
and 
  take_list_date <= '20-12-2017' 
GROUP BY    
  take_list_date 
ORDER BY    
  take_list_date

The problem is I have to include an ID field to make it compatible with Ember Data. When I include an ID field I need to add it to the Group By clause which produces incorrect results.

After looking at some suggestions on other SO questions I tried to resolve this using DISTINCT ON:

select distinct on 
  (take_list_date) take_list_date::varchar as label
  count(customer_id)::int as value 
FROM    
  customer_take_list 
where 
  take_list_date >= '10-12-2017' 
and 
  take_list_date <= '20-12-2017' 
order by 
  take_list_date

Bizarrely this still gives me the same Group By error. What have I done wrong?

Answer 1

I'm not an expert in the technologies involved, but I think you need to create an arbitrary ID rather than use one of the ID's in the table. An example is here: Add Postgres incremental ID. I think your final query should look something like this:

SELECT 
  COUNT(customer_id)::int as value, 
  take_list_date::varchar as label,
  ROW_NUMBER() OVER (ORDER BY take_list_date) AS id
FROM    
  customer_take_list 
where 
  take_list_date >= '10-12-2017' 
and 
  take_list_date <= '20-12-2017' 
GROUP BY    
  take_list_date 
ORDER BY    
  take_list_date