Mysql Count Distinct with Where clause

Question

I am trying to get a count of Ids using some conditions.

This is my initial Query:

SELECT
    COUNT(DISTINCT (id))
FROM
    my_table
WHERE
    app_id = 1
        AND zone_id != 1
        AND presence_time_secs > 20
        AND initial_timestamp BETWEEN '2017-05-02 00:00:00' AND '2017-05-05 00:00:00'
group by 
    day(initial_timestamp)

So my problem is that is not getting the zone_id!=1 part.

There are a few values when I do the same query using zone_id=1, but if in the first query i take the zone_id!=1it will return exactly the same values.

What am I doing wrong?

(I have also tried using NOT IN, same results).

Thanks in advance!

Maybe the zone_id!=1 count is as same as zone_id=1 count. — Blank
– Blank, Commented Jun 22, 2017 at 12:44
But... the zone_id!=1 should take away the results from the other zones correct? — David Pet
– David Pet, Commented Jun 22, 2017 at 12:45
Try to count without AND zone_id != 1 and see the result. I think you should query all the result without aggregation to confirm if data is correct or not. — Blank
– Blank, Commented Jun 22, 2017 at 12:46
Try to see values in this way:SELECT ZONE_ID, COUNT(DISTINCT(Id)) AS RC FROM MYTABLE WHERE app_id = 1 /* AND zone_id != 1 */ AND presence_time_secs > 20 AND initial_timestamp BETWEEN '2017-05-02 00:00:00' AND '2017-05-05 00:00:00' group by day(initial_timestamp) , ZONE_ID — etsa
– etsa, Commented Jun 22, 2017 at 12:47
You're grouping by day so if there are any days that are not zone 1 they will be counted once. I think you're getting 3 each time (though it's hard to guess without any sample data)? — Andrew Bone
– Andrew Bone, Commented Jun 22, 2017 at 13:04

Sergiu Costas · Accepted Answer · 2017-06-22 12:53:22Z

1

Modify this zone_id !=1 with zone_id <> 1 or zone_id NOT LIKE '1'

answered Jun 22, 2017 at 12:53

Sergiu Costas

5604 gold badges9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Pet Over a year ago

tried it but returns the same... at the end i made another select inside the NOT IN and returns different data...

Collectives™ on Stack Overflow

Mysql Count Distinct with Where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related