Reputation: 2954

mysql count help

I am trying to count the number of rows in a table that have an area, how can I achieve this, I currently have this query written,

SELECT DISTINCT area 
  FROM cv 
 WHERE status = 'SHOW' 
   AND is_complete = 'TRUE' 
ORDER BY area ASC

This query currently returns,

area
------------------
West Yorkshire  
Lanchashire

What I am wanting is something like,

area             Number
------------------------
West Yorkshire   19000  
Lancashire       7000

Upvotes: 0

Answers (4)

Reputation: 9784

The sql distinct will list only the diffent values in a table, you original query will need to have count() and group by as well:

SELECT DISTINCT area, count(*) 
  FROM cv
.....

group by area

alternatively, Red Filer and gkrogers answer is good as well.

Upvotes: 0

Reputation: 2812

SELECT area, COUNT(area)
  FROM cv
WHERE status = 'SHOW'
  AND is_complete = 'TRUE'
GROUP BY area

Count of area will count only non null values

Upvotes: 3

Reputation: 8356

SELECT area, COUNT(area)
  FROM cv
WHERE status = 'SHOW'
  AND is_complete = 'TRUE'
  AND area IS NOT NULL
GROUP BY area

Upvotes: 1

Reputation: 171491

select area, count(*)
from cv
where status = 'SHOW' 
    and is_complete = 'TRUE' 
group by area

Upvotes: 4