CODEWITHSUNDEEP
mysqldatabase
Bluejay
Bluejay

Reputation: 351

MySQL - Need to select MIN and MAX with a WHERE clause, as part of a larger query

I have something like this:

SELECT foo1, foo2, foo3, MAX(foo4) AS max FROM table WHERE foo5 = bar;

The thing is, in addition to having the WHERE foo5 = bar clause apply to the whole query, I also want to specify a condition for foo4. I don't just want the maximum value of foo4 from the whole table, but the maximum value of foo4 when another field is equal to something specific. So something like this.

SELECT foo1, foo2, foo3, MAX(foo4 WHERE field=otherthing) AS max FROM table WHERE foo5 = bar;

How can I do this?

EDIT: Here is some more detail. This is my current query:

SELECT Car_Type, Car_Imm, MIN(Use_Kilom) AS Init_Kilom, 
       MAX(Use_Kilom) AS Final_Kilom, MAX(Use_Kilom) 
       - MIN(Use_Kilom) AS Distance_Kilom, null, null, 
       SUM(Use_Amount) AS Amount_Total, 
       SUM(Use_Liter) / 20 AS Liter_Total, null, null 
FROM fueluse, car 
WHERE Use_Car = Car_Code AND 
       (Use_Date BETWEEN '$datefrom' AND '$dateto')

In this case, MAX(Use_Kilom) and MIN(Use_Kilom) are both getting the absolute maximum and minimum values from the entire table. I just want them to get the maximum value for each Use_Car value.

I tried replacing them with the following, respectively:

(SELECT MIN(Use_Kilom) FROM fueluse, car WHERE Use_Car = Car_Code) AS Init_Kilom
(SELECT MAX(Use_Kilom) FROM fueluse, car WHERE Use_Car = Car_Code) AS Final_Kilom

But they're still giving me the absolute minimum and maximum values in the entire fueluse table's Use_Kilom field rather than just for each Use_Car's maximum and minimum values of Use_Kilom. Note that Use_Car and Car_Code are linked as a foreign key.

Upvotes: 0

Views: 366

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

I would expect to see a group by clause then using aggregate functions(min,max,sum etc). In this case probably group by Car_Type, Car_Imm. The implict join should also be changed to an explicit join.

SELECT Car_Type, Car_Imm, MIN(Use_Kilom) AS Init_Kilom, 
       MAX(Use_Kilom) AS Final_Kilom, MAX(Use_Kilom) 
       - MIN(Use_Kilom) AS Distance_Kilom, null, null, 
       SUM(Use_Amount) AS Amount_Total, 
       SUM(Use_Liter) / 20 AS Liter_Total, null, null 
FROM fueluse
join  car on Use_Car = Car_Code AND 
where (Use_Date BETWEEN '$datefrom' AND '$dateto')
group by Car_Type, Car_Imm

It would make things clearer if you qualified your fieldnames with the table names they belong to (or their alias)

Upvotes: 1

Related Questions