I am just learning SQL and working with a MySQL database. My Database has 10 columns: prim, Date, num1, num2, num3, num4, num5, num6, num7, bonus. It is a lotto database. I am trying to make a query that calls the most called numbers and bonus. My current work is:
SELECT * FROM draws WHERE num1, num2, ... this, however, is not giving me what I want. I have been reading through Tutorialpoint on Mysql database but it is no help when it comes to selecting a range of columns. Is this even possible to select a range of columns or am I doing it wrong?
This is a Microsoft SQL Server forum; you might get better answers at a MySQL forum. One way that should work in any variant of SQL is as follows. This may not be the best or most efficient way in MySQL.
SELECT
N,COUNT(*) AS PickCount
FROM
(
SELECT num1 AS N FROM draws UNION ALL
SELECT num2 FROM draws UNION ALL
SELECT num3 FROM draws
) s
GROUP BY N
ORDER BY COUNT(*) DESC;
select num
,sum(c) as pickcount
from ( select num1 as num, count(*) as c from draws group by num1
union all select num2 as num, count(*) as c from draws group by num2
union all select num3 as num, count(*) as c from draws group by num3
union all select num4 as num, count(*) as c from draws group by num4
union all select num5 as num, count(*) as c from draws group by num5
union all select num6 as num, count(*) as c from draws group by num6
union all select num7 as num, count(*) as c from draws group by num7
union all select bonus as num,count(*) as c from draws group by bonus
) as a
group by num
order by pickcount desc