Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

COUNT DISTINCT with CONDITIONS

Tags:

sql

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.

like image 557
derekhh Avatar asked Dec 27 '12 00:12

derekhh


People also ask

How count distinct values with conditions in SQL?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.

Can we use distinct in WHERE condition?

By using the WHERE clause with a DISTINCT clause in MySQL queries, we are putting a condition on the basis of which MySQL returns the unique rows of the result set.

What does count (*) do in SQL?

COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.


3 Answers

You can try this:

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

like image 130
ntalbs Avatar answered Oct 04 '22 04:10

ntalbs


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
like image 22
zaz Avatar answered Oct 04 '22 04:10

zaz


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.

like image 43
MJBLACKEND Avatar answered Oct 04 '22 02:10

MJBLACKEND