AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.2K members online now
2.2K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Count Distinct with MySql not working

Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

Hello,

 

Count Distinct with MySql is not working properly. I managed to tracked what is the root cause for that but I would like to know is that bug a priority to be resolved? As a developer I think that the fix is pretty simple.

 

So when you have report using count distinct with mysql and to the report is not applied time range the sql generated is the following:

 

SELECT COUNT(distinct CAST(Card_ID AS CHAR)) AS calc_2BOx6CGf FROM g_report AS t0 ORDER BY calc_2BOx6CGf ASC;

 

This returns the correct distinct numbers of used cards over the entire dataset. I my case that is 865 unique cards.

 

Unfortunately when you apply any time range to the report the following SQL is generated:

 

SELECT Sale_Date, COUNT(distinct CAST(Card_ID AS CHAR)) AS calc_2BOx6CGf FROM g_report AS t0 GROUP BY Sale_Date ORDER BY calc_2BOx6CGf ASC;

 

Based on that query the report shows you aggregated numbers of unique cards but for each day. I my case for June 2017 the number raise to 1048 that is not correct since I have only 865 unique cards in total.

 

To be resolved the bug the query generated  needs to be similar to:

 

SELECT COUNT(distinct CAST(Card_ID AS CHAR)) AS calc_2BOx6CGf FROM g_report AS t0 WHERE Sale_Date BETWEEN #Range_Start_Date# AND #Range_End_Date# ;

 

I hope the Google team will manage to resolve that issue soon.

 

Regards,

Hristo

 

 

 

 

Count Distinct with MySql not working

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

We are having the same problem with the Postgres connector, which I assume functions similarly.  The unnecessary group by on date makes the count distinct incorrect.  

Count Distinct with MySql not working

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

I have the same issue also connecting to MySQL. 

Please help @Rick E 

Count Distinct with MySql not working

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

I have the same issue. Any news as to whether there is a workaround or when this will be fixed? Seems to be going on for a while already this issue.

Count Distinct with MySql not working

EB
Visitor ✭ ✭ ✭
# 5
EB
Visitor ✭ ✭ ✭

Hi, think i have the same problem, where and how did you find the sql being generated. Sorry if the answer is obvious but I would like to confirm the issue 

Thanks 

Count Distinct with MySql not working

Follower ✭ ☆ ☆
# 6
Follower ✭ ☆ ☆

You're missing the point that your card ids show up on multiple days.  Hence the group by date will give you the daily count.  This is correct functionality and not an issue with GDS.

 

I think you need to pre-aggregate your distinct count of card_ids at the database level for each day, typically looking back 7 (for weekly) or 30 (for monthly).   

 

 

Re: Count Distinct with MySql not working

EB
Visitor ✭ ✭ ✭
# 7
EB
Visitor ✭ ✭ ✭

Is this what we are discussing ?, on March 1st the distinct count is 3 count is 4, however on March 2nd it is the same as the count which is 4. So GDS is checking for distinct within the date grouping

 

countdist.JPG 

Count Distinct with MySql not working

Follower ✭ ☆ ☆
# 8
Follower ✭ ☆ ☆

What you are saying is 'missing the point'. If i have to pre-agregate ... the time range funcitonality will become total crap! The aggregation functions are designed ... to aggregate. 

Count Distinct with MySql not working

Follower ✭ ☆ ☆
# 9
Follower ✭ ☆ ☆

I'm monitoring the sql requests coming from GDS on MySQL.

Count Distinct with MySql not working

EB
Visitor ✭ ✭ ✭
# 10
EB
Visitor ✭ ✭ ✭

@Hristo H Thanks Think i can do that also