CODEWITHSUNDEEP
mysqlmariadb
mesumosu
mesumosu

Reputation: 155

Cast query result as decimal in mysql

What I have:

I have a table visits that has 2 columns userid and ref_url, where userid is the id of the users and ref_url corresponds to the url from which the userid was referred. Empty spaces in the column ref_url corresponds to direct visits and are not considered.

Table: vists

-------------------------------------
|  userid |        ref_url          | 
-------------------------------------
|      1  |                         |
|      1  | https://demosite.com    | 
|      1  | https://demosite2.com   |
|      1  |                         |
|      1  | https://demosite3.com   | 
|      1  |                         |
-------------------------------------

What I expect to achieve:

I want to create a query that will count the total number of ref_url occurrences for a particular userid excluding empty spaces (in the ref_url field), limit the count within 100 and multiply the count with 0.5 and represent the result as DECIMAL on an alias column estimated value.

------------------------------------
|  userid |    estimated value     | 
------------------------------------
|      1  |       1.50             |
------------------------------------

My Query:

SELECT userid,
       CAST(((
              SELECT COUNT(NULLIF(TRIM(ref_url), ''))
                  FROM visits
                  LIMIT 0,100
             ) * 0.5) AS DECIMAL(12,2)) AS 'estimated value'
FROM visits

GROUP BY userid

The above query throws a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2))

The query works when DECIMAL(12,2) is replaced by UNSIGNED.

The query when used with DECIMAL(12,2) outputs with Adminer but results in

[1329] No data - zero rows fetched, selected, or processed

when tried directly on Mariadb. Is there a chance of permission issues?

Upvotes: 1

Views: 585

Answers (1)

Rick James
Rick James

Reputation: 142298

LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say

LIMIT ROWS EXAMINED 100

(Caveat: I have not tested this.)

Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21

As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use

FORMAT(expression, 2)

Upvotes: 1

Related Questions