CODEWITHSUNDEEP
mysqlsql
user3717671
user3717671

Reputation: 1

Listing a count of unique values in a table

I have a MySQL table which has fields to allow users to log different aircraft they see, inlcuding type, registratio, date, location etc. What I want to do is create a query which counts up all the different unique values under the 'type' field and adds them up so users can see how many different 'types' (of aircraft) they have seen. I used the following query;

SELECT `type`,COUNT(*) as count 
FROM `spotted_list` 
GROUP BY type  
ORDER BY count DESC

which works perfectly for the whole database producing results like this;

type            count Descending
B.737-800   802
AIRBUS A320 781
Boeing 737-800  716
A'bus A320  463
A330-200    371

but I want to filter that buy 'username' so I tried this;

SELECT `type`,COUNT(*) as count 
FROM `spotted_list` 
GROUP BY type 
ORDER BY count DESC 
WHERE `username` LIKE 'admin'

so hopeing that would display the above count list but only for the user called 'admin' however this is what i get;

Error
SQL query: Documentation

SELECT  `type` , COUNT( * ) AS count
FROM  `spotted_list` 
GROUP BY TYPE 
ORDER BY count DESC 
WHERE  `username` LIKE  'admin'
LIMIT 0 , 30

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username LIKE 'admin' LIMIT 0, 30' at line 1

Does anyone know what is wrong please??

Upvotes: 0

Views: 82

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 157136

The where should come before the group by:

SELECT  `type` , COUNT( * ) AS count
FROM  `spotted_list` 
WHERE  `username` LIKE  'admin'
GROUP BY TYPE ORDER BY count DESC 
LIMIT 0 , 30

Upvotes: 1

juergen d
juergen d

Reputation: 204924

The order of the keywords is fixed. It is

select
from
join
where
group by
having
order by 
limit

Upvotes: 0

Related Questions