[sql] COUNT DISTINCT with CONDITIONS

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.

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 > 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.

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 > 0
GROUP BY tag

Code counts the unique/distinct combination of Tag & Entry ID when [Entry Id]>0

select count(distinct(concat(tag,entryId)))
from customers
where id>0

In the output it will display the count of unique values Hope this helps

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]>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.