How to store Query Result in variable using mysql

Question

SET @v1 := SELECT COUNT(*) FROM user_rating; SELECT @v1 

When I execute this query with set variable this error is shown.

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(*) FROM user_rating' at line 1  Execution Time : 00:00:00:000 Transfer Time  : 00:00:00:000 Total Time     : 00:00:00:000  (1 row(s) returned) Execution Time : 00:00:00:343 Transfer Time  : 00:00:00:000 Total Time     : 00:00:00:343 

Answer 1

Surround that select with parentheses.

SET @v1 := (SELECT COUNT(*) FROM user_rating); SELECT @v1; 

Answer 2

Additionally, if you want to set multiple variables at once by one query, you can use the other syntax for setting variables which goes like this: SELECT @varname:=value.

A practical example:

SELECT @total_count:=COUNT(*), @total_price:=SUM(quantity*price) FROM items ...