Frequency list of each column in a table

I have table that looks like this:

r01 r02 r03 r04
1   2   X   X
1   2   X   1
X   1   2   1
X   2   2   2
1   2   1   X
1   1   1   2
1   X   1   1
1   2   X   1
2   2   X   2

And I would like to get a frequency array list for each column
(not row), similar to array_count_values()
. Like

r01: 1 => 6, X => 2, 2 => 1
r02: 1 => 2, X => 1, 2 => 6
r03: 1 => 3, X => 4, 2 => 2
r04: 1 => 4, X => 2, 2 => 3

Is it possible to to with one or a few mysql questions? I have not come up with and idea. The only solution I has is to get all data to PHP and then have a data array and just add a one to corresponding counter for each row.

I can have 100->20000 rows. So I would like to have a mysql solution that scales better that a PHP solution.

— Edit

I was displaying a simplified table structure but I think I need to show the full table.

CREATE TABLE IF NOT EXISTS `tips_rows` (
  `row_id` int(11) NOT NULL,
  `r01` enum('1','X','2') NOT NULL,
  `r02` enum('1','X','2') NOT NULL,
  `r03` enum('1','X','2') NOT NULL,
  `r04` enum('1','X','2') NOT NULL,
  `r05` enum('1','X','2') NOT NULL,
  `r06` enum('1','X','2') NOT NULL,
  `r07` enum('1','X','2') NOT NULL,
  `r08` enum('1','X','2') NOT NULL,
  `r09` enum('1','X','2') NOT NULL,
  `r10` enum('1','X','2') NOT NULL,
  `r11` enum('1','X','2') NOT NULL,
  `r12` enum('1','X','2') NOT NULL,
  `r13` enum('1','X','2') NOT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And tips_rows contains all combinations of the values. (1,6 million rows) And to this I have a link table connection user to tips_rows, ´tips_rows_users´

So this link table would hold for a number of users a data set of 100-10000 row_id for each user.

I have found one solution base on other stackoverflow googling. Maybe not the purest query but it works and is fast.

SELECT
    SUM(CASE WHEN r01 = '1' THEN 1 ELSE 0 END) AS r11,
    SUM(CASE WHEN r01 = 'X' THEN 1 ELSE 0 END) AS r1X,
    SUM(CASE WHEN r01 = '2' THEN 1 ELSE 0 END) AS r12,
    SUM(CASE WHEN r02 = '1' THEN 1 ELSE 0 END) AS r21,
    SUM(CASE WHEN r02 = 'X' THEN 1 ELSE 0 END) AS r2X,
    SUM(CASE WHEN r02 = '2' THEN 1 ELSE 0 END) AS r22,
    SUM(CASE WHEN r03 = '1' THEN 1 ELSE 0 END) AS r31,
    SUM(CASE WHEN r03 = 'X' THEN 1 ELSE 0 END) AS r3X,
    SUM(CASE WHEN r03 = '2' THEN 1 ELSE 0 END) AS r32,
    SUM(CASE WHEN r04 = '1' THEN 1 ELSE 0 END) AS r41,
    SUM(CASE WHEN r04 = 'X' THEN 1 ELSE 0 END) AS r4X,
    SUM(CASE WHEN r04 = '2' THEN 1 ELSE 0 END) AS r42,
    SUM(CASE WHEN r05 = '1' THEN 1 ELSE 0 END) AS r51,
    SUM(CASE WHEN r05 = 'X' THEN 1 ELSE 0 END) AS r5X,
    SUM(CASE WHEN r05 = '2' THEN 1 ELSE 0 END) AS r52,
    SUM(CASE WHEN r06 = '1' THEN 1 ELSE 0 END) AS r61,
    SUM(CASE WHEN r06 = 'X' THEN 1 ELSE 0 END) AS r6X,
    SUM(CASE WHEN r06 = '2' THEN 1 ELSE 0 END) AS r62,
    SUM(CASE WHEN r07 = '1' THEN 1 ELSE 0 END) AS r71,
    SUM(CASE WHEN r07 = 'X' THEN 1 ELSE 0 END) AS r7X,
    SUM(CASE WHEN r07 = '2' THEN 1 ELSE 0 END) AS r72,
    SUM(CASE WHEN r08 = '1' THEN 1 ELSE 0 END) AS r81,
    SUM(CASE WHEN r08 = 'X' THEN 1 ELSE 0 END) AS r8X,
    SUM(CASE WHEN r08 = '2' THEN 1 ELSE 0 END) AS r82,
    SUM(CASE WHEN r09 = '1' THEN 1 ELSE 0 END) AS r91,
    SUM(CASE WHEN r09 = 'X' THEN 1 ELSE 0 END) AS r9X,
    SUM(CASE WHEN r09 = '2' THEN 1 ELSE 0 END) AS r92,
    SUM(CASE WHEN r10 = '1' THEN 1 ELSE 0 END) AS r101,
    SUM(CASE WHEN r10 = 'X' THEN 1 ELSE 0 END) AS r10X,
    SUM(CASE WHEN r10 = '2' THEN 1 ELSE 0 END) AS r102,
    SUM(CASE WHEN r11 = '1' THEN 1 ELSE 0 END) AS r111,
    SUM(CASE WHEN r11 = 'X' THEN 1 ELSE 0 END) AS r11X,
    SUM(CASE WHEN r11 = '2' THEN 1 ELSE 0 END) AS r112,
    SUM(CASE WHEN r12 = '1' THEN 1 ELSE 0 END) AS r121,
    SUM(CASE WHEN r12 = 'X' THEN 1 ELSE 0 END) AS r12X,
    SUM(CASE WHEN r12 = '2' THEN 1 ELSE 0 END) AS r122,
    SUM(CASE WHEN r13 = '1' THEN 1 ELSE 0 END) AS r131,
    SUM(CASE WHEN r13 = 'X' THEN 1 ELSE 0 END) AS r13X,
    SUM(CASE WHEN r13 = '2' THEN 1 ELSE 0 END) AS r132
    FROM `tips_rows` AS r
    INNER JOIN tips_rows_users USING (row_id)
    WHERE user__id='{userid}'

This will give me one result row as

r11 r1X r12 r21 r2X r22 r31 r3X r32 r41 r4X r42 r51 r5X r52 r61 r6X r62 r71 r7X r72 r81 r8X r82 r91 r9X r92 r101 r10X r102 r111 r11X r112 r121 r12X r122 r131 r13X r132
40  34  26  48  30  22  69  14  17  70  16  14  15  17  68  28  31  41  80  20  0   49  29  22  38  30  32  69   16   15   29   28   43   19   31   50   13   25   62

And that I can use in my php-template file.

Try this::

Select 'R01', r01, count(1) from myTable group by r01
UNION
Select 'R02', r02, count(1) from myTable group by r02
UNION
Select 'R03', r03, count(1) from myTable group by r03
UNION
Select 'R04', r04, count(1) from myTable group by r04
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Frequency list of each column in a table

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录