How to show big numbers per K (stands for kilo)?

Question

I have a table like this:

// table
+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534431     |
| 2  | peter  | 334        |
| 3  | amos   | 1300       |
| 4  | carter | 13490      |
| 5  | basil  | 1351       |
+----+--------+------------+

Now I want this output:

// newtable
+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534k       |
| 2  | peter  | 334        |
| 3  | amos   | 1.3k       |
| 4  | carter | 13.4k      |
| 5  | basil  | 1.3k       |
+----+--------+------------+

Well, first of all, I want to know, Is it possible to I do that using MySQL? Something like this:

select id, name,
  concat(substr(reputation, 1, 4), IF(LENGTH(reputation) > 4, 'k', '')) as NewRep 
from table

I know the above query is not correct, I just said it as a clue ..!

But if implementing that is not possible using MySQL, then how can I do that using PHP?

if (strlen($result['reputation']) >= 4){
    $NewRep = substr($result['reputation'],0,3);
    $NewRep = round($NewRep).'k';
}

However this ^ solution is incomplete. Because it does not support .5 (point half), and also its sbust() does not work as well.

Have a look: (http://stackoverflow.com/questions/5467694/converting-number-abbreviations-5-2k-1-7m-etc-into-valid-integers-with-php) — Apb, Nov 20 '15 at 11:20

Andrew Bone · Accepted Answer · 2015-11-20T12:28:27.673

4

SELECT 
    id,
    name,
    IF(reputation >= 1000,
        CONCAT(IF(LENGTH(LEFT(CAST(reputation / 100 AS CHAR), LENGTH(reputation)-2)) >= 4,
                    LEFT(reputation, LENGTH(reputation)-3),
                    LEFT(CAST(reputation / 1000 AS CHAR), LENGTH(reputation)-1)),
                'k'),
        reputation) AS reputation
FROM
    table

Something like that would probably do it.

Output:

+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534k       |
| 2  | peter  | 334        |
| 3  | amos   | 1.3k       |
| 4  | carter | 13.4k      |
| 5  | basil  | 1.3k       |
+----+--------+------------+

edited Nov 20 '15 at 12:28

answered Nov 20 '15 at 11:23

Andrew Bone

7,092
2
18
33

I tested your solution and worked correctly, Just two things: The output of first row is `534.4K` while I wanted this: `534k` *(because my purpose of doing that is briefing)*. And really I wonder why the output of `13490` is `13.5k`, I was expecting this `13.4k`. – Shafizadeh Nov 20 '15 at 11:42
So you only want the .5 and above? Or you only want 3 significant figures? The nature of rounding is if the next unit down is 5 or above, the rounded unit goes up. – Andrew Bone Nov 20 '15 at 11:46
No, I don't want just `.5`, your answer is fine. just please add `substr()` to prevent of long numbers. And is there another function for **rounding** as descending? – Shafizadeh Nov 20 '15 at 11:48
OK this does what you want. – Andrew Bone Nov 20 '15 at 12:16
Your solution converts `13490` to `13.K` – Shafizadeh Nov 20 '15 at 12:28
Sorry about that, missed a bit, try it now :-) – Andrew Bone Nov 20 '15 at 12:28
Yeah! worked correctly, and you solved *"bad rounding"* problem. However your solution is too slow. I tested it, the difference between your solution with mine *(for 2 million rows)* is `0.2 sec`. Anyway thanks :-) – Shafizadeh Nov 20 '15 at 12:34
Just one question: What is `AS CHAR` in your answer? – Shafizadeh Nov 20 '15 at 12:49
1

It converts the INT into a CHAR – Andrew Bone Nov 20 '15 at 12:51

score 2 · Answer 2 · edited May 23 '17 at 12:03

2

I stumbled upon this answer by Renaat De Muynck

and I found this solution

http://sqlfiddle.com/#!9/666e16/2

SELECT
  id,
  CASE 
   WHEN number = 0 THEN 0 
   ELSE 
    CONCAT(
      ROUND(number / POW(1000, TRUNCATE(LOG(1000, number), 0)), 0),
      ' ',
      ELT(TRUNCATE(LOG(1000, number), 0) + 1, '', 'k', 'M', 'G', 'T', 'P', 'E', 'Z', 'Y', 'B')
    )
  END
  AS number
FROM test;

edited May 23 '17 at 12:03

Community

1
1

answered Nov 20 '15 at 14:38

yunzen

32,854
11
73
106

Well, Never `number` is not equal to `0` *(zero)*, So please edit your solution. – Shafizadeh Nov 20 '15 at 14:41
Huh? I cannot take the `LOG(1000, number)` when `number` equals zero. So I have to make a branch to prevent errors – yunzen Nov 20 '15 at 14:45
Listen, `number` column never accept `0` value. Its default value is `1`. – Shafizadeh Nov 20 '15 at 14:46
That maybe the case with your setting, but this error case must be part of the solution. The other question is: Does it work? Does it create correct answers? – yunzen Nov 20 '15 at 14:48
Ok, But your answer is still incomplete. because I want some decimal numbers like these: `1.3k`, `13.4k`. But all numbers in your solution are integer *(round)*. – Shafizadeh Nov 20 '15 at 14:51
To change the length of the decimal cyphers, change the seconf argument to the `ROUND` function: `ROUND(123.45,0)` --> 123, `ROUND(123,45) --> 123.5` – yunzen Nov 20 '15 at 15:03

Shafizadeh · Answer 3 · 2015-11-21T19:50:58.780

0

Try this:

Query1:

SELECT id, name, 
   IF(reputation >= 1000,
      CONCAT(
         IF(LENGTH(ROUND(reputation/1000, 1)) > 4,
            ROUND(reputation/1000),
            TRUNCATE(reputation/1000,1)),
      "K"),
   reputation) as reputation  
FROM `table`

Query2: (This is more readable)

SELECT id, name, 
    CASE WHEN reputation >=100000  THEN  CONCAT( ROUND   ( reputation /1000 ),     "k" ) 
         WHEN reputation >=1000    THEN  CONCAT( TRUNCATE( reputation /1000, 1 ),  "k" ) 
         ELSE reputation
    END AS reputation
FROM `table`

Query3: (shorter and faster)

SELECT id, name,
    CASE WHEN value >= 1000 THEN
         CONCAT(TRIM(TRAILING '.' FROM SUBSTR(TRUNCATE(number/1000, 1), 1, 4)), 'k')
         ELSE value
    END as reputation
FROM `table`

Output: (for all of queries)

// newtable
+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534k       |
| 2  | peter  | 334        |
| 3  | amos   | 1.3k       |
| 4  | carter | 13.4k      |
| 5  | basil  | 1.3k       |
+----+--------+------------+

edited Nov 21 '15 at 19:50

answered Nov 20 '15 at 12:25

Shafizadeh

9,960
12
52
89

@AndrewBone You are right, "Rounding" in my solution isn't as good as yours. But mine is more faster instead. – Shafizadeh Nov 20 '15 at 12:45
So, if I understand you correctly, you would rather have a fast query then a correct query? Even if the fast (and incorrect) query is only 0.2 seconds faster? Are you sure you got your priorities right? – HoneyBadger Nov 20 '15 at 13:59
@HoneyBadger No, don't get me wrong..! My solution is not incorrect. It works as well. Just "Rounding" in my solution is not (exactly) the same with OP (myself) wants in the question. Well, as I told, OP is me, and a fast query is much more important for me than this small difference between `1.3k` and `1.4k`. Both of them are correct (almost) – Shafizadeh Nov 20 '15 at 14:03
I don't know the situation, so you probably are the best judge in this case, but I don't consider the difference between 1.3k and 1.4k small... – HoneyBadger Nov 20 '15 at 14:05
@HoneyBadger I edited my answer and my query executes **exactly** the same output with what OP wants in the question. Happy now?! ;-) – Shafizadeh Nov 20 '15 at 14:38

How to show big numbers per K (stands for kilo)?

3 Answers3

Linked