how to mysql group by zerofill?

Question

SELECT count(w.c1) AS count
     , w.c1 AS color
FROM
  data w
GROUP BY
  w.c1
ORDER BY
  w.id DESC
LIMIT
  50000;

I'm wondering, is there any kind of mysql query to group by zerofill values, i have all values in c1 as rgb code '0002500034' (r=000/g=250/b=034), after query it shows like 000250034->250034

Why not save them in HEX or better in binary with an int ? – Alexandre Lavoie Nov 15 '12 at 10:58 — Alexandre Lavoie, Nov 15 '12 at 10:58
Did you define your `c1` column as `zerofill`? – juergen d Nov 15 '12 at 11:01 — juergen d, Nov 15 '12 at 11:01
Please provide `SHOW CREATE TABLE data` – Rick James Feb 08 '19 at 06:00 — Rick James, Feb 08 '19 at 06:00

score 1 · Accepted Answer · answered Nov 15 '12 at 11:56

1

What about the LPAD MySQL function ? LPAD(str, len, padstr)

SELECT count(w.c1) AS count
 , LPAD(w.c1, 9, '0')  AS color
FROM
  data w
GROUP BY
  w.c1
ORDER BY
  w.id DESC
LIMIT
  50000;

You can also turn your column type into CHAR(9).

answered Nov 15 '12 at 11:56

Blackarma

46
2

vagiz · Answer 2 · 2012-11-16T04:34:07.587

SELECT count(w.c1) AS count
     , concat(w.c1) AS color
FROM
  data w
GROUP BY
  w.c1
  order by w.c1 desc
LIMIT
  50000;

just tried and it works fine, also i'll try your version

edit: my query:

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 16.599000 |
+-----------------+-----------+

vs your's

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 3.599000 |
+-----------------+----------+

how to mysql group by zerofill?

2 Answers2