CODEWITHSUNDEEP
mysqlcountmax
Luis Alvarado
Luis Alvarado

Reputation: 9396

MySQL Max Count without Order By

I have the following MySQL line:

SELECT age, count(*) AS total FROM pacient WHERE age BETWEEN 20 AND 40 GROUP BY age ORDER BY age and I need to add an additional column to it that shows ONLY the max value of the count(*) for every row. For example:

13 2 7
18 2 7
23 5 7
24 7 7
26 6 7
32 3 7
38 1 7
41 1 7
46 4 7

This would be 3 columns and the 3rd column shows 7 since 7 was the highest number in the second column where the count(*) is made.

Upvotes: 2

Views: 4489

Answers (4)

DRapp
DRapp

Reputation: 48139

select 
      p.Age,
      count(*)  CountPerAge,
      max(ar.AllRecs) AllRecs
   from
      pacient p,
      ( select count(*) AllRecs 
           from pacient p2
           where p2.age between 20 and 40 ) ar
   where 
      p.age between 20 and 40
   group by
      p.age

By doing a join to the second "subselect" with no join condition, it will give a Cartesian result... Since it is a count with no group by, it will always return a single record and thus be joined to all age rows otherwise. The MAX() of the value is no problem since it is the only record will just be returned as-is.

Upvotes: 1

Patrick Maciel
Patrick Maciel

Reputation: 4944

Here the solution:

select age,
    count(*),
    (select max(c) from 
        (select count(*) as c from pacient where age between 20 and 40 group by age) as x) as t 
from pacient 
where age between 20 and 40 
group by age 
order by age;

Upvotes: 4

Sahal
Sahal

Reputation: 4136

It is always good to use SQL VIEWS instead of using sub queries. Because VIEW will be having already compiled result.

CREATE VIEW subqueryView 
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age

SELECT A.age, A.total, MAX(A.total) as max_value FROM (SELECT FROM subqueryView) as A
GROUP BY A.age, A.total

Upvotes: 0

VAShhh
VAShhh

Reputation: 3504

Have you tried to wrap your query with another query? something like

SELECT A.age, A.total, MAX(A.total) as max_value FROM (
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age) as A
GROUP BY A.age, A.total

Upvotes: 1

Related Questions