CODEWITHSUNDEEP
mysqlsqlcountmax
Rabcor
Rabcor

Reputation: 249

How to select the MAX from the COUNT in SQL

enter image description here

That is a picture of my table.

I must select "Fastanumer" of all cars where "Tegund" is the most common value (which is Toyota in this example)

This is the code i tried 

SELECT Fastanumer FROM  `Bill` 
WHERE Tegund =  
(SELECT MAX(y.cnt) FROM (SELECT COUNT(Tegund) AS cnt FROM Bill ) AS y)

Which i had to work pretty hard to figure out only to end up beating myself in the head over the fact that MAX will only turn into a number. (And since Tegund isn't a list of numbers...)

Is this even possible? How can i do this?

Upvotes: 3

Views: 1512

Answers (4)

tuffkid
tuffkid

Reputation: 1323

I guess it should work this way:

SELECT Fastanumer 
FROM  `Bill` 
WHERE Tegund = (
    SELECT Tegund 
    FROM (
        SELECT Tegund,COUNT(*) FROM Bill GROUP BY Tegund ORDER BY COUNT(*) DESC LIMIT 1
    ) t1
)

Or even like this:

SELECT Fastanumer 
FROM  `Bill` 
WHERE Tegund = (
    SELECT Tegund FROM Bill GROUP BY Tegund ORDER BY COUNT(*) DESC LIMIT 1
)

Upvotes: 4

fthiella
fthiella

Reputation: 49049

Here's my solution:

SELECT Bill.*
FROM Bill
WHERE Tegund IN (
  SELECT Tegund
  FROM Bill
  GROUP BY Tegund
  HAVING COUNT(*) = (
    SELECT MAX(cnt) FROM (
      SELECT COUNT(*) cnt
      FROM Bill
      GROUP BY Tegund
    ) s
  )
)

A little more complicated than others, but if more than one Tegund shares the same number of rows, this query will show all Tegunds which are the most common.

Please see fiddle here or here.

Upvotes: 1

marty
marty

Reputation: 4015

Depends on the DB (and associated SQL). Try

select fastanumber from bill
    inner join
(select count(*) as cnt, tegund from Bill group by tegund) grpby
    on bill.tegund = grpby.tegund
    and grpby.cnt = (select max(cnt) from (select count(*) as cnt, tegund from Bill group by tegund))

Upvotes: 0

Ian R. O'Brien
Ian R. O'Brien

Reputation: 6920

What you want to do first is figure out which Tegund appears the most in your table. That is what the subquery is doing. Then you will select the Fastanumer which matches that Tegund.

SELECT DISTINCT Fastanumer 
FROM 'BILL'
WHERE Tegund = (
    SELECT TOPT 1 Tegund, 
                  COUNT(*) as Count
    FROM `BILL`
    GROUP BY Tegund
    ORDER BY Count DESC)

Upvotes: 0

Related Questions