Kristoffer
Reputation: 626
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.
Upvotes: 3
Views: 158
Answers (3)
Kristoffer
Reputation: 626
Thanks for all the different solution. I have now done some testing on my data. I have done a query for 10000rows. And here is my findings.
Solution 1:
Select 'R01', r01, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r01
UNION
Select 'R02', r02, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r02
UNION
Select 'R03', r03, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r03
UNION
Select 'R04', r04, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r04
UNION
Select 'R05', r05, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r05
UNION
Select 'R06', r06, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r06
UNION
Select 'R07', r07, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r07
UNION
Select 'R08', r08, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r08
UNION
Select 'R09', r09, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r09
UNION
Select 'R10', r10, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r10
UNION
Select 'R11', r11, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r11
UNION
Select 'R12', r12, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r12
UNION
Select 'R13', r13, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r13
Takes ~0.1365s every time to query
Solution 2:
SELECT n,
SUM(IF(r01 = n, 1, 0)) AS 'r01',
SUM(IF(r02 = n, 1, 0)) AS 'r02',
SUM(IF(r03 = n, 1, 0)) AS 'r03',
SUM(IF(r04 = n, 1, 0)) AS 'r04',
SUM(IF(r05 = n, 1, 0)) AS 'r05',
SUM(IF(r06 = n, 1, 0)) AS 'r06',
SUM(IF(r07 = n, 1, 0)) AS 'r07',
SUM(IF(r08 = n, 1, 0)) AS 'r08',
SUM(IF(r09 = n, 1, 0)) AS 'r09',
SUM(IF(r10 = n, 1, 0)) AS 'r10',
SUM(IF(r11 = n, 1, 0)) AS 'r11',
SUM(IF(r12 = n, 1, 0)) AS 'r12',
SUM(IF(r13 = n, 1, 0)) AS 'r13'
FROM `tips_rows` AS r
INNER JOIN tips_rows_users USING (row_id)
JOIN (SELECT '1' AS n UNION SELECT '2' UNION SELECT 'X') S
WHERE user_id='27'
GROUP BY n;
Takes ~0.0997s first time to query. Could be cached in mysql query cache so second time takes ~0.0002s
Solution 3:
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='27'
Takes ~0.0587s first and second time. After that 0.0002s for question after that.
I will go with solution 3. As it has the best worst case time. But it is strange that it takes two queries before cached.
Upvotes: 0
Sashi Kant
Reputation: 13465
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
Upvotes: 1
Sylvain Leroux
Reputation: 51990
Assuming only 4 columns and 3 possible values per column, and if you really want your result presented as "one column per value":
SELECT 'R01' AS 'Row', SUM(IF(r01 = 1, 1, 0)) as '1',
SUM(IF(r01 = 2, 1, 0)) as '2',
SUM(IF(r01 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R02', SUM(IF(r02 = 1, 1, 0)) as '1',
SUM(IF(r02 = 2, 1, 0)) as '2',
SUM(IF(r02 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R03', SUM(IF(r03 = 1, 1, 0)) as '1',
SUM(IF(r03 = 2, 1, 0)) as '2',
SUM(IF(r03 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R04', SUM(IF(r04 = 1, 1, 0)) as '1',
SUM(IF(r04 = 2, 1, 0)) as '2',
SUM(IF(r04 = 'X', 1, 0)) as 'X'
FROM myTable
Producing:
+------+----+----+---+
| ROW | 1 | 2 | X |
+------+----+----+---+
| R01 | 6 | 1 | 2 |
| R02 | 2 | 6 | 1 |
| R03 | 3 | 2 | 4 |
| R04 | 4 | 3 | 2 |
+------+----+----+---+
See http://sqlfiddle.com/#!2/c6376/2
If pivoting the result table is acceptable, the following query will perform much better:
SELECT n,
SUM(IF(r01 = n, 1, 0)) AS 'r01',
SUM(IF(r02 = n, 1, 0)) AS 'r02',
SUM(IF(r03 = n, 1, 0)) AS 'r03',
SUM(IF(r04 = n, 1, 0)) AS 'r04'
FROM mytable JOIN (SELECT '1' AS n UNION SELECT '2' UNION SELECT 'X') S
GROUP BY n;
Resulting in:
+----+------+------+------+-----+
| | R01 | R02 | R03 | R04 |
+----+------+------+------+-----+
| 1 | 6 | 2 | 3 | 4 |
| 2 | 1 | 6 | 2 | 3 |
| X | 2 | 1 | 4 | 2 |
+----+------+------+------+-----+
Upvotes: 0
Related Questions
- how to show frequency of a column value where the value of other columns are a certain value?
- How to get repeated number's frequency in a column of a table in SQL?
- MySQL query to generate frequency counts for multiple columns
- Loop through columns and run frequency (count*) on each column in MySQL
- Count of a count- PHP MYSQL
- Trying to use Mysql to count frequency in this example php?
- Return frequency of string in MYSQL column with PHP
- How to sort Sql column by frequency and select the row
- Mysql count the frequency of the frequency of ocurrences
- calculate the frequency