How to add comma to numbers in MySQL?

Question

Is there a way to get MySQL to return a value with a comma? I have this MySQL query which returns values with 5 digits, for example 14123, but I want it to be 14.123. I read something about the FORMAT() function but I'm not really sure how to use it together with my query, and I want to avoid using PHP.

My query looks like this:

public function artists_count_get()  
    { 
        $this->load->database();
        $sql = "SELECT COUNT( DISTINCT artist_id ) AS artists FROM artists";
        $query = $this->db->query($sql);
        $data = $query->result();

        if($data) {
            $this->response($data, 200); 
        } else {
            $this->response(array('error' => 'There was an error'), 404);
        }
    }

Also, as suggested in the question I marked this a duplicate of, you should ask yourself why you are avoiding doing the formatting in the presentation layer (PHP). — Dan Bechard, Mar 24 '14 at 15:05
@Dan I use BackboneJS as frontend and Restful API, so I have no use for PHP, sorry for not mentioning... — SHT, Mar 24 '14 at 15:07
I've tidied up the question a bit. You may wish to improve it further, as the title requests a comma and the body requests either a comma or a dot separator, depending on which bit you read. — halfer, Mar 24 '14 at 18:02

score 4 · Answer 1 · answered Mar 24 '14 at 15:07

4

Try this:

$sql = "SELECT FORMAT(COUNT( DISTINCT artist_id ),0) AS artists FROM artists";

More info about using FORMAT: MySQL FORMAT

answered Mar 24 '14 at 15:07

segarci

739
1
11
19

is it maybe possible to change the comma to a dot? – SHT Mar 24 '14 at 15:20
In MySQL v5.6 FORMAT, you can specify a locale: [MySQL FORMAT](http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_format) – segarci Mar 24 '14 at 17:32

score 4 · Answer 2 · answered Mar 24 '14 at 15:08

FORMAT(X,D) Formats the number X to a format like '###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. Try

$sql = "SELECT FORMAT(COUNT( DISTINCT artist_id ), 0) AS artists FROM artists";

How to add comma to numbers in MySQL?

2 Answers2