CODEWITHSUNDEEP
phpmysqlsqldatabase
Paul Hesketh
Paul Hesketh

Reputation: 95

Database most common entries query

having had assistance that helped show result, its not showing the results as i need them.

I have this

Table name = power Column names = power1, power2, power2, power4, power5

 $res = mysql_query("select power1, power2, power2, power4, power5, 
 count(*) as cnt 
 from power 
 group by power1, power2, power2, power4, power5 
 order by cnt desc limit 5;");

which shows a particular row from the table rather than just the 5 most common numbers from the table

the table entries

the results

as you can see, rather than show the top 5 numbers, which would should at least show numbers 1,5 & 2, it shows the 2nd row.

any ideas would be greatly received.

Many thanks

Paul

Upvotes: 0

Views: 216

Answers (3)

Thomas
Thomas

Reputation: 64635

Select Z.Num, Count(*) As Cnt
From    (
        Select power1 As Num From power
        Union All Select power2 From power
        Union All Select power3 From power
        Union All Select power4 From power
        Union All Select power5 From power
        ) As Z
Group By Z.Num
Order By Count(*) Desc
Limit 5

Make sure you use Union All or else you will get 1 as the count for each discrete number.

SQL Fiddle version

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146409

To get the top 5 most common values in all columns, you have to join to the table 5 times

 Select top 5 val From
 (Select power1 val From table
  Union Select power2 val From table
  Union Select power3 val From table
  Union Select power4 val From table
  Union Select power5 val From table) z
 Group By val
 Order By Count(*) Desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

To get the 5 most common values in column col in table x:

select col, count(*) as cnt
from x
group by col
order by cnt desc
limit 5;

If you are trying to get the five most common numbers in the five columns (a major change to the question after my original answer), here is one method:

select power, count(*) as cnt
from (select (case when n = 1 then power1
                   when n = 2 then power2
                   when n = 3 then power3
                   when n = 4 then power4
                   when n = 5 then power5
              end) as power
      from power p cross join
           (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
           ) n
     ) p

This puts all the columns into a single column and then returns the most common values.

Upvotes: 5

Related Questions