The table structure is like this:
actions: int(10)
unlock: tinyint(1)
user_id: int(20)
name: varchar(50)
I have such query:
SELECT SUM(actions) AS "sum_actions", SUM(unlock) AS "sum_unlock", user_id, name
FROM mytable AS `Results` WHERE user_id != 0 GROUP BY user_id
ORDER BY sum_actions DESC LIMIT 0,300
This gives #1064 - You have an error in your SQL syntax error.
When I remove SUM(unlock) AS "sum_unlock" then query works. So I thought that it is not possible to summing TINYINTs. So I changed to COUNT(unlock) as "count_unlock" but this didn't help. I don't want to change "unlock" table to INT because it only has boolean values. How can I count the unlock table with summing for each user_id ?
unlock is a reserved word. Try this:
SELECT SUM(actions) AS "sum_actions", SUM(`unlock`) AS "sum_unlock", user_id, name
FROM mytable AS `Results`
WHERE user_id != 0
GROUP BY user_id
ORDER BY sum_actions DESC
LIMIT 0,300
Here is a list of reserved words.