I saw some questions similar but I still couldn't figure out.
There is many columns, but to short, I want to count distinct by g1 column. I thought I could just use COUNT(DISTINCT)...?
Please help me for this problem.
Thank you so much in advance
| G1 | C1 | expected results |
|---|---|---|
| 1 | A | 2 |
| 1 | A | 2 |
| 1 | B | 2 |
| 2 | A | 3 |
| 2 | B | 3 |
| 2 | A | 3 |
| 2 | C | 3 |
| 3 | A | 1 |
| 3 | A | 1 |
| 3 | A | 1 |
| 3 | A | 1 |
4 Answers
Most (all?) databases do not support using COUNT(DISTINCT ...) as an analytic function. So in this case, I would suggest just joining to a subquery which finds the distinct counts:
SELECT t1.G1, t1.C1, t2.cnt FROM yourTable t1 INNER JOIN ( SELECT G1, COUNT(DISTINCT C1) AS cnt FROM yourTable GROUP BY G1 ) t2 ON t2.G1 = t1.G1 ORDER BY t1.G1, t1.C1; You can get a windowed count distinct, but you always need two functions, one possible way is:
SELECT G1, C1, max(dr) over (partition by G1) as cnt FROM ( SELECT G1, C1, dense_rank() over (partition by G1 order by C1) AS dr FROM yourTable ) as dt Depending on your data and actual query this might perform better than Tim's query :-)
Of course, this can be modified for NULLable columns flagging the 1st occurance:
SELECT G1, C1, sum(flag) over (partition by G1) as cnt FROM ( SELECT G1, C1, case when lag(C1) over (partition by G1 order by C1) = C1 then null else C1 end as flag FROM yourTable ) as dt You can use the below query:
SELECT count(C1) from TableName Group by G1 If your database doesn't use count(distinct) as a window function, you can use the handy trick of the sum of dense_rank():
select t.*, (-1 + dense_rank() over (partition by g1 order by c1 asc) + dense_rank() over (partition by g1 order by c1 desc) ) as expected from t; Given that count(distinct) as a window function is easily implemented this way, I am surprised that many databases do not support this functionality directly.
One nuance: This counts NULL as a valid value. You don't have NULL values in your sample data so I don't think this affects you. But, if you wanted an exact equivalent:
select t.*, ( (case when count(*) over (partition by g1) = count(c1) over (partition by g1) then -1 else -2 end) + dense_rank() over (partition by g1 order by c1 asc) + dense_rank() over (partition by g1 order by c1 desc) ) as expected from t; 
