0

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 ?

trante
  • 33,518
  • 47
  • 192
  • 272

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That worked thank you. But it worked like this: SUM('unlock') When I changed it to SUM("unlock") it didn't work. What is the difference between *"* and *`* for SQL queries. – trante Apr 15 '13 at 20:29
  • 1
    @trante . . . For some reason, MySQL allows double quotes for string literals. Most databases only allow singla quotes and double quotes play the role of back quotes in MySQL. That's just the way it is designed, I guess. – Gordon Linoff Apr 15 '13 at 20:31
0

You can try SUM(CAST(unlock AS INT)) to count as if the column was an INT column without actually changing it to be an INT column:

SELECT
    SUM(actions) AS "sum_actions",
    SUM(CAST(unlock AS INT)) AS "sum_unlock",
    user_id,
    name 
FROM
    mytable AS `Results`
WHERE
    user_id != 0
GROUP BY
    user_id,
    name
ORDER BY
    sum_actions DESC
LIMIT 0,300
MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263