1

Assuming the following query:

SELECT meta_value as xyz, COUNT(*) AS count
  FROM wp_postmeta
  WHERE meta_key = "xyz" AND meta_value != ""
  GROUP BY meta_value
  HAVING count > 1

The query works fine, but I don't need the count column in my results. How can the count column be excluded?

I know I can use a subquery (as shown below), but I'd like to know if there's a better way.

SELECT xyz FROM (
  SELECT meta_value as xyz, COUNT(*) AS count
    FROM wp_postmeta
    WHERE meta_key = "xyz" AND meta_value != ""
    GROUP BY meta_value
    HAVING count > 1
) AS t
rinogo
  • 8,491
  • 12
  • 61
  • 102
  • 3
    `HAVING count > 1` is using the alias count from the select... So you could substitute `count(*)` instead of the alias in the HAVING clause and eliminate count(*) and the alias from the select. Thus it becomes something like `SELECT meta_value as xyz... HAVING count(*) > 1` – xQbert Jul 19 '17 at 16:26
  • The first answer in this question should solve your problem: [stackoverflow.com/questions/7151401/...](https://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1). Move the `COUNT(*)` to your `HAVING` clause... – War10ck Jul 19 '17 at 16:28
  • @xQbert add it as an answer – Shadow Jul 19 '17 at 16:32
  • @xQbert - It works! Thank you! If you'd like to add an answer, I'll accept it. If it's better to mark this as a duplicate or delete it, that's cool with me, too. – rinogo Jul 19 '17 at 16:35

1 Answers1

3

HAVING count > 1 is using the alias count from the select... So you could substitute count(*) instead of the alias (count) in the HAVING clause and eliminate count(*) and the alias from the select. Thus it becomes something like 

SELECT meta_value as xyz
... 
HAVING count(*) > 1

Order of operations matter here. Having works after the select thus it could use the alias in the select or it can run the count at time of execution. Same thing with order by. But a group by could never use an select alias within the same statement.

xQbert
  • 34,733
  • 2
  • 41
  • 62