MySQL query not giving expected results

Question

I am new to MySQL and I am trying to solve a problem for a project.

I have three tables: cars, sales and customers. cars has VID as pk and some other attributes like model and price. customers has CID as pk and some attributes. sales has (VID,CID) as pk and a sales price and sales date as attributes.

The question is: "What is the model of car, that was sold most often in 2014"?

Here is my query

Select Model
From Cars,Sales
Where Cars.VID=Sales.VID
Group By Cars.VID 
Having MAX( (Select count(*)
         From Cars,Sales Where Cars.VID=Sales.VID AND SellDate Between  '2014-01-01' And '2014-12-30'));

I still get all the models, that exist in the sales table, Could tell me what is wrong?

Gordon Linoff · Accepted Answer · 2016-10-22 13:34:12Z

1

First: Never use commas in the FROM clause. Always use explicit, proper, standard JOIN syntax with an appropriate ON clause.

If you want only one such model, the simplest way is ORDER BY and LIMIT:

Select c.Model
From Cars c JOIN
     Sales s
     on c.VID = s.VID
where s.SellDate >=  '2014-01-01' And
      s.SellDate < '2015-01-01'
Group By c.Model
Order by count(*) desc
limit 1;

Additional notes:

The columns in the GROUP BY should match the unaggregated columns in the SELECT, pretty much always.
Use table aliases that are abbreviations of the table names.
Qualify all column names, so you (and others) know where they come from.
Note the change in the date arithmetic. Last I heard, 2014-12-31 was in 2014 (your business rules may be different). I prefer < to between, in case the "date" column also has a time stamp.

answered Oct 22, 2016 at 13:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

antonis arvanitis Over a year ago

for what reason is the limit 1 used?

Rahul Over a year ago

@antonisarvanitis, cause you asked in question What is the model of car and not What are the models of car

Collectives™ on Stack Overflow

MySQL query not giving expected results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related