Counting TINYINT values in MySQL

Question

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 ?

You also need to add name into your GROUP BY.

Kermit
– Kermit

2013-04-15 18:34:44 +00:00
Commented Apr 15, 2013 at 18:34 — Kermit
– Kermit, Commented Apr 15, 2013 at 18:34

Gordon Linoff · Accepted Answer · 2013-04-15 18:54:11Z

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.

answered Apr 15, 2013 at 18:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

trante Over a year ago

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.

Gordon Linoff Over a year ago

@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.

MarcinJuraszek · Accepted Answer · 2013-04-15 18:36:31Z

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

answered Apr 15, 2013 at 18:36

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

1 Comment

trante Over a year ago

I still get #1064 - You have an error in your SQL syntax error.

Collectives™ on Stack Overflow

Counting TINYINT values in MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related