0

In the MySQL solution here. I am trying to choose Distinct for column DevelopmentName but also want to only pull a count where the name occurs more than once. I'm getting a syntax error and have tried a comma between the column name and the Count instruction?

SELECT DevelopmentName count(*) as Counter
  FROM sunshinemls_property_res WHERE Counter > 2
   AND City = 'Bonita Springs' OR City = 'Estero'
    OR City = 'Naples' OR City = 'Marco Island'
 ORDER BY DevelopmentName ASC
Community
  • 1
  • 1
Rocco The Taco
  • 3,695
  • 13
  • 46
  • 79
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Jan 17 '15 at 12:20

2 Answers2

3

There are several small and logical errors in your query:

  • Missing comma in the select
  • Bad logic in the where (if the where worked, it wouldn't work because you would need parens for the logic you want)
  • > 2 when you want > 1
  • Missing group by clause

The biggest problem, though, is the use of counter in a where clause. That logic should go in the having:

SELECT DevelopmentName, count(*) as Counter
FROM sunshinemls_property_res
WHERE City IN ('Bonita Springs', 'Estero', 'Naples', 'Marco Island')
GROUP BY DevelopmentName
HAVING count(*) > 1
ORDER BY DevelopmentName ASC;

In MySQL, you can say HAVING Counter > 1. However, not all databases support that syntax, so I usually just repeat the aggregation expression.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Perfect, thanks for the detailed explanation too especially with the counter...that is a huge help moving forward with this. – Rocco The Taco Jan 17 '15 at 15:45
-1

It will definitely work

select Distinct(DevelopmentName),count(*) as Counter from sunshinemls_property_res where City in ('Bonita Springs', 'Estero','Naples','Marco Island') Group by DevelopmentName Having Counter >= 2;

You can not use Counter alias in where clause because mysql tries to search column name for that table. So alias is not allowed to use in where clause. So to avoid you can use group by and having clause.

eLemEnt
  • 1,741
  • 14
  • 21