CODEWITHSUNDEEP
mysqlsqldatabase
Alex Duguy
Alex Duguy

Reputation: 23

how to count and select a max value of the count mysql

I have this code :

select count(quantite) from lignescommandes  where quantite ='1' 
union
select count(quantite)  from lignescommandes   where quantite ='2' 
union
select count(quantite)  from lignescommandes  where quantite ='3' ;

I want to select the highest value of the table that is returned but i don't see how to do that.

Thanks in advance for your help

Upvotes: 2

Views: 789

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would suggest:

select count(*)
from lignescommandes
where quantite in ('1', '2', '3')
group by quantite
order by count(*) desc
limit 1;

In other words, you are basically replicating an aggregation query with union which seems silly.

Also, if quantite (as its name implies) is a number, then drop the single quotes. Don't mix numbers and strings.

And, if the only three values are 1, 2, and 3, the where clause is not needed at all.

Upvotes: 0

forpas
forpas

Reputation: 164069

You don't need UNION.

You can do it in only 1 scan of the table with conditional aggregation and the function GREATEST():

SELECT GREATEST(
         COUNT(CASE WHEN quantite ='1' THEN quantite END),
         COUNT(CASE WHEN quantite ='2' THEN quantite END),
         COUNT(CASE WHEN quantite ='3' THEN quantite END)
       ) AS max_count
FROM lignescommandes

or with SUM() instead of COUNT():

SELECT GREATEST(
         SUM(quantite ='1'),
         SUM(quantite ='2'),
         SUM(quantite ='3')
       ) AS max_count
FROM lignescommandes

Upvotes: 3

Luuk
Luuk

Reputation: 14899

select max(c)
from (
   select count(quantite) as c from lignescommandes  where quantite ='1' 
   union
   select count(quantite)  from lignescommandes   where quantite ='2' 
   union
   select count(quantite)  from lignescommandes  where quantite ='3' 
   ) x

Upvotes: 0

Related Questions