Hot answers tagged mysql-5.5
Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem from creeping back into the data. You should define the 4 columns as a non-unique index now since this process will involving lots of comparisons using them. ...
You cannot use @variable_name to declare variable. @ can be used only for session variables. Change your code to this, it will work DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE batchsize BIGINT DEFAULT 1; END //
You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable. Change the name of your variable from @batchsize to batchsize, and then read up on MySQL ...
For the alternative way to count these: select day(date) as day, hour(date) as hour, count(1) as c, sum(a=1) as a1, sum(a=0) as a0, (sum(a=1) / count(1)) as pct from t group by hour, date(date) I added the date to the group by so the same hour of different days do not get counted together - change that the way you need.
Only top voted, non community-wiki answers of a minimum length are eligible