Mysql ORDER BY COUNT each value on every column field

Question

Each table field is set as 00 00 00 00 00 00. I am trying to find a solution to order by count each of the 00 00 00 00 00 00 number in each column field. The code below works ok but it is ugly and I am not able to order the results. Thanks!

DESIRED RESULT (example)

Number - Times it appears in column

01 - 100
02 - 99
03 - 98

COLUMN SAMPLE

if ($stmt = $post_con->prepare('SELECT asw FROM tb WHERE CONCAT(" ", asw, " ") LIKE CONCAT("% ", ?, " %")')) {


    for($i = 1; $i < 60; $i++){

                $stmt->bind_param("s", $de);
                $de = sprintf('%02d', $i);

                $stmt->execute();
                $stmt->store_result();
                $qty = $stmt->num_rows;

                /* bind result variables */
                $stmt->bind_result($asw);
                $stmt->fetch();

                echo $qty.' -> '.$de.'</br>';

                $stmt->close();


        }

Are you saying 1) an example row from `tb` might have a value like `00 00 00 00 01`? 2) you want to count the individual occurences of `00`, `01`, ..etc. in the whole table? (so if my example row was the only row, the output would be `00 -> 401 -> 1`? — , Apr 23 '16 at 01:11
No. I want to count how many times 00 or 01 (and each of the six digit number) appears in the column not in the field. Thanks @Terminus — Tom, Apr 23 '16 at 01:20
Would you mind showing some example rows (just like 3 or 4) and the expected output from those rows? It might be possible to do this all in a single query but i need to see a slightly more complete example. — , Apr 23 '16 at 01:22
Sure, the example is in the question. The result should be ex > number 01 appears X times, number 02 appears X times and so on. Thanks — Tom, Apr 23 '16 at 01:26
I see now that the quest already has the sample output. Could you include some sample rows with some sample data? — , Apr 23 '16 at 01:27
I'd say it's a dupe. Here's a [sqlfiddle](http://sqlfiddle.com/#!2/103bb4/5) that uses the dupe to produce the results you want. 1 query. It's sick. Note how it does `SELECT 1 UNION ALL ... SELECT 6` It only goes up to 6 because there are 6 numbers within your record. if you add another, you'll have to add a ` UNION ALL SELECT 7`. If you agree with my assessment, be sure to go over to that question and give him an upvote. — , Apr 23 '16 at 02:27
Thanks for your solution @Terminus. It works fine, how can I give you +1 ? — Tom, Apr 24 '16 at 18:20
I'll write up an answer tonight. Accept it whenever you get a chance. — , Apr 24 '16 at 18:21
Thanks @Terminus. What if I want to count 2 or more numbers. Example: Now we are checking only one. Thanks again! — Tom, Apr 24 '16 at 19:43
Re: count 2 or more. Is that count if 2 are on the same row? Another example would be useful. — , Apr 24 '16 at 21:06
I mean, given 04 18 21 25 38 57 to a = 04, b = 18, c = 21, d = 25, e = 38, f = 57. Can we do the same with every combination? Example: with A, with A B, A B C, A B C D and so on? Thanks! @Terminus — Tom, Apr 25 '16 at 13:37
could you ask another question for that 2nd question with an example and what you've tried? It'll be easier to help if you write up the question with more details — , Apr 26 '16 at 02:16
Hi @Terminus. I've just prepared a new question. If you want to give a check. Thanks! http://stackoverflow.com/questions/37122881/mysql-count-every-combination-and-find-match-in-column — Tom, May 09 '16 at 18:30

cpugourou · Answer 1 · 2016-04-23T01:04:54.190

0

Something more of best practice would be but hard to tell without more details as per what you are looking for:

$stmt = $post_con->prepare('SELECT asw, count(asw) as count FROM tb WHERE CONCAT(" ", asw, " ") LIKE CONCAT("% ", ?, " %") GROUP BY asw ORDER BY count DESC LIMIT 1');
for ($i = 1; $i < 60; $i++) {
    $stmt->bind_param(1, sprintf('%02d', $i));
    $stmt->execute();
    $obj = $stmt->fetchall(PDO::FETCH_ASSOC);
    if (count($obj) !== 0) {
        echo $de . '->' . $obj['count'] . '</br>';
    }
}
$stmt->close();

edited Apr 23 '16 at 01:04

answered Apr 23 '16 at 00:57

cpugourou

775
7
11

I want something better to replace this 'for loop' and order the results. Thanks cpu! – Tom Apr 23 '16 at 01:04
loop is fine. Dont close your stmt within the loop or you loose all the benefit of the prepared stmt. depends if your are looking for a set of results or only one. this example is for only one (highest count). – cpugourou Apr 23 '16 at 01:06
It is set in the query. Either desc or asc. Depends on what you need. – cpugourou Apr 23 '16 at 01:21
If the you want the complete result sorted, remove limit 1. – cpugourou Apr 23 '16 at 01:22
And add a foreach whithin the if count!=0 to echo each values. Please please do not use while. Stick to foreach for arrays... just my 2 cents. ;-) – cpugourou Apr 23 '16 at 01:23

score 0 · Accepted Answer · edited Mar 13 '22 at 10:00

You can use SQL to do more of the work for you. You can apply the query from this question to your situation.

The subquery breaks all the numbers into their own row. It uses the SELECT 1 UNION ALL ... SELECT 6 to find the 1st number, ..., and the 6th number and put each one into its own row. (Note that you have to go up to 6 because you have 6 numbers per row. If you had more, you would have to adjust the query accordingly.)

From there it's as simple as GROUP BY on the number, COUNT()ing the unique occurrences, and ORDER BY the number in ASCending order.

SELECT num, COUNT(num)
FROM (
  SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tb.asw, ' ', numbers.n), ' ', -1) num
  FROM
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    ) AS numbers
  INNER JOIN tb
    ON CHAR_LENGTH(tb.asw)
       -CHAR_LENGTH(REPLACE(tb.asw, ' ', ''))>=numbers.n-1
) numNumbers
GROUP BY num
ORDER BY num ASC

Mysql ORDER BY COUNT each value on every column field

2 Answers2