I have a simple table of 7 columns
Week ¦ 1st ¦ 2nd ¦ 3rd ¦ 4th ¦ 5th ¦ 6th ¦
Each week, my father adds Saturdays UK lottery numbers to a simple PHP script that I created. He has early onset Alzheimers and tries to keep his brain active. Tonight he asked me a question about the database. He asked me if it was possible to see the 6 most popular numbers.
I tried to create a simple SQL query:
SELECT 1st, 2nd, 3rd, 4th, 5th, 6th, COUNT(*) AS 'foo' FROM `dad` GROUP BY 1st, 2nd, 3rd, 4th, 5th, 6th ORDER BY foo DESC
But the results weren't as I expected.
1st 2nd 3rd 4th 5th 6th foo
2 6 8 32 33 35 1
3 6 12 17 35 40 1
3 6 31 43 46 53 1
etc
What I hoped would happen would be for the table to merge into one column, and then count and have a simple result, something like:
Number Count
2 1
3 2
6 3
8 1
And then maybe put it in ascending order. I can then use that SQL query to create a simple table for him to show the most common numbers.
I'm thinking of doing a general SQL query
SELECT 1st FROM `dad`
Then creating an Array with the results, then adding
SELECT 2nd FROM `dad`
To the end of the Array and continuing for all 6 columns, then using PHP to count the numbers individually.
Is there a quicker way?
Your first effort should go into fixing your data model. Each number should be stored on a separate row rather than in a column, like:
week pos num
1 1 6
1 2 8
1 3 32
Then your query would be a simple aggregate query:
select num, count(*) no_picks from dad group by num order by no_picks desc
For your given table structure, you would need to unpivot the columns to rows. In MySQL, you can use union all for this:
select num, count(*) no_picks
from (
select `1st` num from dad
union all select `2nd` from dad
union all select `3rd` from dad
union all select `4th` from dad
union all select `5th` from dad
union all select `6th` from dad
) t
group by num
order by no_picks
You can do:
select n, cnt from ( select n, count(*) as cnt from ( select `1st` as n from `dad` union all select `2nd` from `dad` union all select `3rd` from `dad` union all select `4th` from `dad` union all select `5th` from `dad` union all select `6th` from `dad` ) x group by n ) y order by cnt desc limit 6