SQLTeam.com | Weblogs | Forums

Selecting all columns


#1

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?


#2

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;

#3

Alternative:

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

#4

Thank you both for helping despite that I posted in the wrong place. Both examples worked fine.