DilTeam DilTeam - 1 year ago 63
MySQL Question

Query works in Oracle by not in Mysql

This query works in Oracle:

select count(city) - count (distinct city) from station;

But doesn't work in MySQL. What's the MySQL equivalent of this query? At the risk of pointing out obvious, this query returns the 'difference' between 'total no. of cities' on the station table and number of distinct cities.

Answer Source

It should work just fine if you remove space after second count

select count(city) - count(distinct city) from station;

Here is a SQLFiddle

To use the name as a function call in an expression, there must be no whitespace between the name and the following “(” parenthesis character.

See 10.2.4 Function Name Parsing and Resolution

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download