what percentage of ids in my table have value 0-CodePudding

Suppose I have the following table:

 users(id INT, timestamp TIMESTAMP, speed DOUBLE PRECISION);

INSERT INTO users(id, timestamp, speed)
VALUES (658,'2016-04-01 00:50:43.673 01',0.93),
(658,'2016-04-01 00:50:45.677 01',0.94),
(658,'2016-04-01 00:50:56.813 01',0.94),
(658,'2016-04-01 00:51:13.332 01', 0),
(658,'2016-04-01 00:51:18.337 01',0),
(658,'2016-04-01 00:51:23.427 01',0),
(658,'2016-04-01 00:51:28.584 01',0),
(658,'2016-04-01 00:51:33.574 01',0),
(658,'2016-04-01 00:51:38.686 01',0),
(658,'2016-04-01 00:51:43.719 01',0)

I have over 6000 distinct ids. How do I calculate number of ids having 50% of rows speed equal to 0?

CodePudding user response：

This query should work:

SELECT id FROM users GROUP BY id HAVING SUM(CASE speed WHEN 0 THEN 1 ELSE 0 END) * 2 >= COUNT(speed)

Grouping by user id with GROUP BY,
To count for a special value, I'm using the case function to map the value to 1 and the other values to 0 and take the sum of the mapped values. Now we have the count of the searched value for each id.
I use HAVING keyword to take only id where the number of searched value is 50% of rows.

Shorter version:

SELECT id FROM users GROUP BY id HAVING SUM(CASE speed WHEN 0 THEN 2 ELSE 0 END) >= COUNT(speed)