Count Distinct with Conditions

COUNT DISTINCT with CONDITIONS

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.

SQL count distinct with a condition based on a different column

You need a distinct count of exams, so I think that is:

select candidate, qualification, 
       count(distinct units) as total_units,
       count(distinct case when exam_status = 'Passed' then exam end)
from example_table
group by candidate, qualification;

If you want to sum the units of the passed exams, this becomes trickier. I would recommend window functions:

select candidate, qualification, count(distinct unit),
       sum(case when exam_status = 'Passed' and seqnum = 1 then unit end) as total_units,
       count(distinct case when exam_status = 'Passed' then exam end)
from (select et.*,
             row_number() over (partition by candidate, qualification, exam 
                                order by (case when exam_status = 'Passed' then 1 else 2 end)
                               ) as seqnum
      from example_table et
     ) et
where seqnum = 1
group by candidate, qualification;

SQL: Count distinct column combinations with multiple conditions

With this query:

select distinct state, storeid, item
from mytable
where date between '2020-01-01' and '2020-03-31' and units > 0

you get all the distinct combinations of state, storeid and item under your conditions and you can aggregate on it:

select state, count(*) q1total
from (
  select distinct state, storeid, item
  from mytable
  where date between '2020-01-01' and '2020-03-31' and units > 0
) t
group by state

See the demo.

Results:

> state    | q1total
> :------- | ------:
> new york |       3
> oregon   |       2

COUNT DISTINCT WITH CONDITION and GROUP BY

You can try using row_number()

select status,count(distinct name) as cnt from 
(
select name,date,status,row_number() over(partition by name order by date desc) as rn
from tablename
)A where rn=1 and status is not null
group by status

Distinct Count & Distinct Count with Condition in a Single Query

You can use COUNT DISTINCT with a case statement. Something like this...

SELECT COUNT(DISTINCT ColumnName)
      , COUNT(DISTINCT ( CASE WHEN SomeCondition = True THEN ColumnName ELSE NULL END)) FilteredDistCount 
FROM TableName 

Count unique values, with condition in other column

I think I found a way, but there might be cleaner sollutions...:

In column E, I merged the x-coordinate, y-coordinate, and date. By:

=A2&"-"&B2&"-"D2"

In the first row, I wrote a 1, as this is always unique/first occurence of this combination. For the rest i dragged down the following.

=IF(COUNTIF($E$2:$E35;J36);0;1)

It checks if this combination has already occured above, if yes, then 0 else 1.

---

Related Topics