CODEWITHSUNDEEP
mysqlsql
Passionate Engineer
Passionate Engineer

Reputation: 10412

MySQL where search issue

I cannot seem to search when I add WHERE statement below. But it works without it....

Is there something wrong?

Below is my code: Thank you.

SELECT DISTINCT (
                    `name`
                    ), users.id, `email`, `state_id`,
                    MAX(`total_time_driven_at_this_trip`) AS trip
                    FROM `users`
                    LEFT JOIN trip_vics ON users.id = trip_vics.user_id
                    GROUP BY `user_id`
                    WHERE name LIKE '%dan%'

Upvotes: 0

Views: 50

Answers (3)

Edgar Velasquez Lim
Edgar Velasquez Lim

Reputation: 2446

The WHERE keyword can't be used with aggregate functions. Use HAVING instead http://www.w3schools.com/sql/sql_having.asp

Put WHERE before the group by if you want to apply the filter before MAX is calculated.

Upvotes: 1

zerkms
zerkms

Reputation: 255025

You need to place your where right before group by

Upvotes: 3

HJW
HJW

Reputation: 23443

having goes with group by.

having name like '%dan%'

Or if you are actually trying to have a where clause independent of the having clause, it should go between the join and the group by statement.

Upvotes: 0

Related Questions