using having in where clause

Question

If I have the following table:

my_table:    
  varchar name
  int     score

and I want to do the following query:

select name from my_table group by name having count(*)>3
union
select name from my_table where name like '%xyz'

Is there a way doing this query without a union?

To be clear: I want all names which have more than three entries in the table OR answer the like clause '%xyz'.

So, to clarify, what results do you want? Your edit suggests you want all names where score is greater then 3? Can you gave an example of what you would like the results to look like? — Drazisil, Apr 12 '15 at 07:58

druidicwyrm · Accepted Answer · 2015-04-12T08:01:58.393

1

The below query will only grab names that match the like clause before grouping:

SELECT name,count(*) as num FROM my_table GROUP BY name HAVING num > 3 OR name like '%xyz'

EDIT: The above query has been altered to allow for either the name or the num clauses to cause the row to be accepted.

edited Apr 12 '15 at 08:01

answered Apr 12 '15 at 07:51

druidicwyrm

score 0 · Answer 2 · edited Apr 12 '15 at 09:07

0

select name,count(*) as num 
from my_table 
group by name having num>3
where name like '%xyz'

edited Apr 12 '15 at 09:07

Toby Allen

answered Apr 12 '15 at 07:43

Adi

I believe this select won't return the same results as the union above. It will return the names which answer both having and like clause while the one above is a union between the two groups of records which answer one of them. – Avner Levy Apr 12 '15 at 07:52

Drazisil · Answer 3 · 2015-04-12T08:09:50.037

0

Unions are only used when linking two tables.

I think you are looking for something like this?

SELECT name,count(*) as num 
FROM my_table 
GROUP BY name 
HAVING num > 3 OR name like '%xyz'

Edited to match OP's question after reading intended results.

edited Apr 12 '15 at 08:09

answered Apr 12 '15 at 07:51

Drazisil

3 Answers3