OPEN WITH CONDITIONS

Question

I want to count the number of individual elements in a column subject to a certain condition, for example, if the table is as follows:

tag | entryID ----+--------- foo | 0 foo | 0 bar | 3 

If I want to count the number of different tags as a "tag counter" and count the number of different tags with an input identifier> 0 as a "positive tag counter" in the same table, what should I do?

Now I am counting on two different tables, where in the second table I selected only those rows with entry ID greater than zero. I think there should be a more compact way to solve this problem.

Answer 1

You can try the following:

 select count(distinct tag) as tag_count, count(distinct (case when entryId > 0 then tag end)) as positive_tag_count from your_table_name; 

The first count(distinct...) is simple. The second one looks a bit complicated, actually the same as the first, except that you use the case...when clause. In the case...when clause, you only filter positive values. Zeros or negative values ​​will be null and will not be counted.

It should be noted that this can be done by reading the table once. When it seems to you that you need to read the same table twice or more, this can be done by reading once, in most cases. As a result, it will complete the task much faster with fewer I / O operations.

Answer 2

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 

Answer 3

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 a tag, the second will be the whole score, the third will be positive.

Answer 4

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 clause in the left join and not in the where clause to make sure that all elements that have only entry id of 0 are properly counted in the first DISTINCT.