Count distinct column case when/conditional

Question

I'm trying to count the distinct number of ids in a column and this works fine.

COUNT(DISTINCT messages.id) AS link_created

But when I try to count with a conditional, I get a syntax error, what's the proper syntax to add a case when or some other condition to only count the distinct message ids where the messages.link_label is present?

COUNT(DISTINCT messages.id CASE WHEN messages.link_label IS NOT NULL 1 END) AS link_created

My full query looks like this.

@customers = Customer.select("customers.*,
                                  COUNT(DISTINCT recipient_lists.id) messages_sent,
                                  COUNT(DISTINCT messages.id CASE WHEN messages.link_label IS NOT NULL 1 END) AS link_created,
                                  COALESCE(SUM(video_activities.video_watched_count),0) AS watched_count,
                                  COALESCE(SUM(video_activities.response_count),0) AS response_count,
                                  COALESCE(SUM(video_activities.email_opened_count),0) AS email_opened_count,
                                  COALESCE(SUM(CASE WHEN video_activities.video_watched_at IS NOT NULL THEN 1 ELSE 0 END),0) AS unique_watches,
                                  COALESCE(SUM(CASE WHEN video_activities.email_opened_at IS NOT NULL THEN 1 ELSE 0 END),0) AS unique_opens,
                                  COALESCE(SUM(CASE WHEN video_activities.response_count > 0 THEN 1 ELSE 0 END),0) AS unique_responses,
                                  customers.updated_at AS last_login,
                                  SUBSTRING( email from POSITION( '@' in email) + 1 for length(email)) AS company")
                          .joins("LEFT JOIN messages ON customers.id = messages.customer_id
                                  LEFT JOIN recipient_lists ON messages.id = recipient_lists.message_id AND messages.link_label is NULL               
                                  LEFT JOIN video_activities ON messages.id = video_activities.message_id")
                          .group("customers.id")

Answer 1

Try this:

COUNT(DISTINCT CASE
    WHEN messages.link_label IS NOT NULL
    THEN messages.id
    ELSE NULL END)
AS link_created