I have a hobby project where I am playing with lottery numbers. I have a database with a list of numbers and the columns are named: number1, number2, number3, number4, number5, number6

I want to try do some statistical analysis on these numbers, so I'm trying to get each number and count how many times it occurred. I can do it on each column one by one but want to try find a way to do it in one query.

For example, for one column I go:

SELECT number1, COUNT(number1) FROM lotto_numbers group by number1;

So obviously I can do this 6 times, then get the 6 results into 6 lists and then write some python code to combine all 6 lists to get my answer, but I think its just cleaner to try do it in one query.

So is it possible to count all 6 columns and output the combined results in a format such as:

1: 435
2: 234
3: 353
4: 244
...etc

Where the left number is the number, and the right number is the count of how many times that number appears across all 6 columns?

CodePudding user response：

I hope this works for you.

SELECT "1" as Number, NumCount FROM 
(select count(CASE WHEN number1 IS NOT NULL THEN 1 ELSE 0 END) 
AS NumCount from numberTable)t1 
union all
SELECT "2" as Number,NumCount FROM   
(select count(CASE WHEN number2 IS NOT NULL THEN 1 ELSE 0 END) 
AS NumCount from numberTable)t2 
union all
SELECT "3" as Number, NumCount FROM 
(select count(CASE WHEN number3 IS NOT NULL THEN 1 ELSE 0 END) 
AS NumCount from numberTable)t3;

CodePudding user response：

This might be not the best solution but it works:

with tbl as (select number1 number, count(number1) count
from numbers
group by number1
union all
select number2 number, count(number2) count
from numbers
group by number2
union all
select number3 number, count(number3) count
from numbers
group by number3
union all
select number4 number, count(number4) count
from numbers
group by number4
union all
select number5 number, count(number5) count
from numbers
group by number5
union all
select number6 number, count(number6) count
from numbers
group by number6)
select number, sum(count)
from tbl
group by number

Data:

Result: