Ad Code

MySQL SUM DISTINCT with Conditional

SELECT COUNT(DISTINCT advertiser_id) AS advertiser_qty,
        COUNT(DISTINCT id) AS contract_qty,
        COUNT(DISTINCT advertiser_id , status = 1) AS current_qty,
        SUM(IF(status = 2, 1, 0)) AS expired_qty,
        SUM(IF(status = 3, 1, 0)) AS other_qty
 FROM (SELECT *
       FROM   `contracts`
       GROUP BY advertiser_id, id) AS temp
 DEMO HERE
 SELECT
    COUNT( DISTINCT advertiser_id ) AS advertiser_qty,
    COUNT( DISTINCT id ) AS contract_qty,
    (select count(distinct advertiser_id) from contracts where status =1 
    ) AS current_qty,

   SUM( IF( status = 2, 1, 0 ) ) AS expired_qty,
   SUM( IF( status = 3, 1, 0 ) ) AS other_qty
   FROM (
   SELECT * FROM  `contracts`
   GROUP BY advertiser_id, id
   ) AS temp 
DEMO HERE 
select
  count(distinct tag) as tag_count,
  count(distinct (case when entryId > 0 then tag end)) as positive_tag_count
from
  your_table_name; 

Post a Comment

0 Comments