SELECT  DISTINCT suscribers.email,
                    listas.nombre AS lnombre,
                    clientes.id as idcliente,
                    cliente,
                    listas.id_lista as idlista,
                    sum( case when suscribers.verified=1 and suscribers.activo=1 and suscribers.id_lista=listas.id_lista then 1 else 0 end ) AS verificados,
                    sum( case when suscribers.bounced=1 and suscribers.activo=0 and suscribers.id_lista=listas.id_lista then 1 else 0 end ) as rebotes,
                    sum( case when suscribers.complaint=1 and suscribers.id_lista=listas.id_lista then 1 else 0 end ) as quejas,
                    sum( case when suscribers.activo=1 and suscribers.id_lista=listas.id_lista  then 1 else 0 end ) as activos,
                    sum( case when suscribers.source=2  then 1 else 0 end ) as from_cartacx,
                    sum( case when suscribers.source=3  then 1 else 0 end ) as from_web,
                    sum( case when suscribers.source=5  then 1 else 0 end ) as from_suscriber,
                    sum( case when suscribers.source=1  then 1 else 0 end ) as from_manual,
                    sum( case when suscribers.es_cliente=1 then 1 else 0 end ) as tclientes,
                 sum( case when suscribers.es_cliente=1 and suscribers.activo=1  then 1 else 0 end ) as aclientes,
                 sum( case when suscribers.es_cliente=0 and suscribers.activo=1 then 1 else 0 end ) as prospectos,
                    count( DISTINCT suscribers.email) as total,
                    count( DISTINCT suscribers.email)-sum( case when suscribers.activo=0 then 1 else 0 end ) as atotal, 
		 sum(case when last_email_open between  DATE_SUB(now(), INTERVAL 12 MONTH) and now() then 1 else 0 end) as activos_12m,
		 sum(case when last_email_open between  DATE_SUB(now(), INTERVAL 6 MONTH) and now() then 1 else 0 end) as activos_6m,
	         sum(case when suscribers.`name` is not null or suscribers.lastname is not null then 1 else 0 end) as con_nombre
                FROM clientes
                INNER JOIN suscribers on clientes.id=suscribers.id_cliente 
                LEFT OUTER JOIN listas on suscribers.id_lista=listas.id_lista
                WHERE clientes.active=1 
                GROUP BY clientes.id, listas.id_lista
                ORDER BY cliente ASC