Trying to Count specific items in QUERY

Question

Hi, I currently am returning a table that looks like this:

Country  Result
------   -----
Japan    sunk
Japan    sunk
USA      ok
USA      ok

with country and result being the column names

Id like to know how many times japan and sunken show up? So the resulting query I am trying to achieve is:

country      numSunk
-------      ------
Japan        2
USA          0

I've tried grouping or doing a where result="sunk" but I keep getting errors. Any thoughts? Thanks.

Answer 1

This should work:

SELECT country, COUNT(*) AS sunk
FROM table
WHERE result = "sunk"
GROUP BY country

Edit: if you want 0-rows then you can do:

SELECT country, SUM(IF(result = "sunk", 1, 0)) AS sunk
FROM table
GROUP BY country

Answer 2

select country, count(country)
...
where result = "sunk"
group by country

Assuming this is a SQL query

Answer 3

SELECT country, COUNT(CASE result WHEN 'sunk' then 1 ELSE NULL END) as numSunk
FROM myTable
GROUP BY country

The count will only be incremented when the result is 'sunk'.

Answer 4

Try something like this:

SELECT country, count(*) numSunk
FROM table
WHERE result = 'sunk'
GROUP BY country
UNION
SELECT DISTINCT country, 0 
FROM table t
WHERE NOT EXISTS
  (SELECT 1 FROM table WHERE t.country = country AND result = 'sunk')

The first SELECT gives you all the countries with a sunk result along with the sunk count. The unioned second SELECT gives you all the countries that have no sunk result.