# Multiple Count Dinstinct in one Reflection

**URL:** <https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406>\
**Category:** Uncategorized\
**Created:** [December 10, 2018, 10:38am UTC](https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406 "2018-12-10T10:38:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![marco](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/marco/32/1556_2.png) [@marco](https://community.dremio.com/u/marco)\
**Post date:** [December 10, 2018, 10:38am UTC](https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406/1 "2018-12-10T10:38:21Z")

</div>

Why when i try to run a reflection to calculate two different count distinct with different cases, it doesn’t accelerate, but when i run the same reflection to calculate only one count it is accelerated correctly.

That’s my query

SELECT  
TO\_CHAR(CAST(Record\_anagrafiche\_anagrafiche\_last.accesso\_gialla AS TIMESTAMP),‘YYYY-MM-DD’) AS record\_anagrafiche\_anagrafiche\_last\_accesso\_gialla\_date\_1,  
COUNT(DISTINCT CASE WHEN (Record\_anagrafiche\_anagrafiche\_last.lista\_tipo\_contratti LIKE ‘%FA%’) AND (Record\_anagrafiche\_anagrafiche\_last.lista\_tipo\_contratti NOT LIKE ‘%DIRFA%’ AND Record\_anagrafiche\_anagrafiche\_last.lista\_tipo\_contratti NOT LIKE ‘%BUS%’ OR Record\_anagrafiche\_anagrafiche\_last.lista\_tipo\_contratti IS NULL) THEN Record\_anagrafiche\_anagrafiche\_last.codice\_cliente ELSE NULL END) AS record\_anagrafiche\_anagrafiche\_last\_count\_app\_family\_1,  
COUNT(DISTINCT CASE WHEN (Record\_anagrafiche\_anagrafiche\_last.lista\_tipo\_contratti LIKE ‘%BUS%’) THEN Record\_anagrafiche\_anagrafiche\_last.codice\_cliente ELSE NULL END) AS record\_anagrafiche\_anagrafiche\_last\_count\_app\_business\_1  
FROM Telepass.Looker.Record\_Anagrafiche.anagrafiche\_last AS Record\_anagrafiche\_anagrafiche\_last

WHERE  
(Record\_anagrafiche\_anagrafiche\_last.accesso\_gialla IS NOT NULL)  
GROUP BY TO\_CHAR(CAST(Record\_anagrafiche\_anagrafiche\_last.accesso\_gialla AS TIMESTAMP),‘YYYY-MM-DD’)  
ORDER BY 1 DESC  
LIMIT 7

---

<div class="post-metadata">

**Author:** ![anthony](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/anthony/32/670_2.png) [@anthony](https://community.dremio.com/u/anthony)\
**Post date:** [December 10, 2018, 1:21pm UTC](https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406/2 "2018-12-10T13:21:15Z")

</div>

Is there a [query profile you can share](https://www.dremio.com/tutorials/share-query-profile-dremio/) of both 1 and 2 countdistincts?  
What type of reflection are you using on `Telepass.Looker.Record_Anagrafiche.anagrafiche_last` ?

---

<div class="post-metadata">

**Author:** ![marco](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/marco/32/1556_2.png) [@marco](https://community.dremio.com/u/marco)\
**Post date:** [December 10, 2018, 1:42pm UTC](https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406/3 "2018-12-10T13:42:25Z")

</div>

It’s a Raw reflection

---

<div class="post-metadata">

**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\
**Post date:** [December 10, 2018, 3:22pm UTC](https://community.dremio.com/t/multiple-count-dinstinct-in-one-reflection/2406/4 "2018-12-10T15:22:22Z")

</div>

Have you tried an aggregation reflecrion with the approximate distinct count measure type enabled? Note that you have to enable this as it is off by default.