Total count by List (Subscribed, Ubsubscribed)

MailChimp

 

MailChimp is a cloud-based email marketing solution, that allows designing and sending marketing emails.

Query Text

SELECT LM."ListId" List_Id, L."Name" List_Name,
       SUM(case when "Status" = 'Subscribed' then 1 else 0 end) Subscribed,
       SUM(case when "Status" = 'Unsubscribed' then 1 else 0 end) Unsubscribed,
       COUNT(LM."ListId") Total
       FROM "ListMembers" LM 
        INNER JOIN "Lists" L ON LM."ListId"=L."Id"
       GROUP BY "ListId"
       ORDER BY L."Name"
  

Open Query