COUNT DISTINCT with CONDITIONS

Question

I want to count the number of distinct items in a column subject to a certain condition  for example if the table is like this   tag   entryID ---- --------- foo   0 foo   0 bar   3   If I want to count the number of distinct tags as  tag count  and count the number of distinct tags with entry id   0 as  positive tag count  in the same table  what should I do   I m now counting from two different tables where in the second table I ve only selected those rows with entryID larger than zero  I think there should be a more compact way to solve this problem

User · Accepted Answer

You can try this   select   count distinct tag  as tag count    count distinct  case when entryId  gt  0 then tag end   as positive tag count from   your table name    The first count distinct     is easy  The second one  looks somewhat complex  is actually the same as the first one  except that you use case   when clause  In the case   when clause  you filter only positive values  Zeros or negative values would be evaluated as null and won t be included in count   One thing to note here is that this can be done by reading the table once  When it seems that you have to read the same table twice or more  it can actually be done by reading once  in most of the time  As a result  it will finish the task a lot faster with less I O

User · Answer

This may also work   SELECT      COUNT DISTINCT T tag  as DistinctTag      COUNT DISTINCT T2 tag  as DistinctPositiveTag FROM Table T     LEFT JOIN Table T2 ON T tag   T2 tag AND T entryID   T2 entryID AND T2 entryID  gt  0   You need the entryID condition in the left join rather than in a where clause in order to make sure that any items that only have a entryID of 0 get properly counted in the first DISTINCT

User · Answer

Code counts the unique distinct combination of Tag  amp  Entry ID when  Entry Id  0  select count distinct concat tag entryId    from customers where id gt 0   In the output it will display the count of unique values Hope this helps

User · Answer

This may work   SELECT Count tag  AS  Tag Count  FROM Table GROUP BY tag   and  SELECT Count tag  AS  Negative Tag Count  FROM Table WHERE entryID  gt  0 GROUP BY tag

User · Answer

Try the following statement   select  distinct A  Tag        count A  Tag   as TAG COUNT        SELECT count    FROM  TagTbl  AS B WHERE A  Tag  B  Tag  AND B  ID  gt 0       from  TagTbl  AS A GROUP BY A  Tag    The first field will be the tag the second will be the whole count the third will be the positive ones count

[sql] COUNT DISTINCT with CONDITIONS

Examples related to sql