• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

MySQL Query - Maximum Frequency

Hi all,

I have a project with a table that contains information of various car models. The data schema is:

Table Name: CarModelTbl
Four fields:
sDate [date]
Model [varchar(50)]
EngineSize [varchar(50)]
Colour [varchar(50)]

The table currently only contains 7 records as displayed below:

2010-07-24, Honda,1.6,Red
2010-07-24,Ford,1.6,Green
2010-07-24,Toyota,1.6,Blue

2010-07-25,Audi,3.2,Red
2010-07-25,BMW,1.8,Green
2010-07-25,Toyota,1.4,Blue
2010-07-25,VW,1.4,Blue

I currently work out the Maximum frequency for a model which occurred the most for July 2010 and this is 2. This is calculated with  the query below:

SELECT Max(A.no_count) FROM (SELECT COUNT(*) as no_count FROM CarModelTbl WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)) as A;

The above query works as expected - But now I have a requirement to also display the model which occurred the most and in this case it would Toyota.

Any ideas how to update the query....

Thanks in advance.

 






 


0
mikesung99
Asked:
mikesung99
  • 2
1 Solution
 
George K.Commented:
It should be like this
select countmodel.cc, carmodel.model
(
SELECT Max(A.no_count)  cc FROM (SELECT COUNT(*) as no_count FROM CarModelTbl WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)) as A;
) countmodel
left outer join
(
SELECT model, COUNT(*) as no_count FROM CarModelTbl WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)
)   carmodel
on  countmodel.cc=carmodel.no_count
0
 
George K.Commented:
A correction: 
select countmodel.cc, carmodel.model
from
(

SELECT Max(A.no_count)  cc FROM (SELECT COUNT(*) as no_count FROM CarModelTbl WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)) as A;

) countmodel

left outer join

(

SELECT model, COUNT(*) as no_count FROM CarModelTbl WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)

)   carmodel

on  countmodel.cc=carmodel.no_count

0
 
cyberkiwiCommented:
SQL Server 2008

;WITH TMP AS (
  SELECT Model, COUNT(*) as no_count
  FROM CarModelTbl
  WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25"
  Group by Model
  HAVING COUNT(*)>0
)
SELECT Model, no_count
from TMP
Where no_count = (select max(no_count) from TMP)
0
 
mikesung99Author Commented:
Hi georgekl

Thanks the response - I've tested your solution and it worked as expected thank you.

All I did was to remove the closing statement i.e. ';' and it worked illustrated below is the solution iimplemented.

select countmodel.cc, carmodel.model

from
(

SELECT Max(A.no_count)  cc FROM (SELECT COUNT(*) as no_count FROM ptTest.pt_test WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)) as A

) countmodel

left outer join

(

SELECT model, COUNT(*) as no_count FROM ptTest.pt_test WHERE sDATE >= "2010-07-24" AND sDATE <="2010-07-25" Group by Model HAVING ( COUNT('Model ') > 0)

)   carmodel

on  countmodel.cc=carmodel.no_count;


Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now