Basically I have a list of numbers like:


And I want to perform a query like this for each of the previous numbers:

SELECT COUNT(*), field_name
FROM my_table
WHERE field_name = NUMBER

Where "NUMBER" will be 1, 2 ... 5 in this example. So I expect a result containing 5 rows and 2 columns.

Right now I'm doing the task this way:

(SELECT ... field_name = 1) UNION (SELECT ... field_name = 2 ) ... (SELECT ... field_name = 5)

Is there any alternative way to do this while still using a single query? In particularly I don't like how the last query may become very long as my list of numbers grows.


What if the query to execute would be something like:

FROM my_table
WHERE field_name > NUMBER

Using the same mechanism as above but this time let's image that this query will return more than 1 row at every execution.

Is there any way to create a single, elegant, query to do this job instead of using "UNION" multiple times?

  • For performance you would be better using UNION ALL as UNION will try to remove duplicate rows, even if they don't exist in your actual data. May 12, 2014 at 12:54
  • @Michael, Thanks for the suggestion, I didn't know about this. May 12, 2014 at 12:59
  • 1
    Your edit makes no sense. You can use what the answer provided, SELECT COUNT(*), field_name FROM my_table WHERE field_name > NUMBER GROUP BY field_name ; May 12, 2014 at 13:05

1 Answer 1


You can use the IN statement

SELECT COUNT(*), field_name
FROM MY_table
WHERE field_name IN (1,2,3,4,5)
GROUP BY field_name
  • Actually I can't, it produces different results. In my test case all the "COUNT(*)"s were summed for example. May 12, 2014 at 11:20
  • GROUP BY .... May 12, 2014 at 11:21
  • Add "GROUP BY field_name" to the above example and you should get the result you desire. May 12, 2014 at 11:21
  • Sorry I forgot the GROUP BY field_name. That should work May 12, 2014 at 11:21
  • Great, it works for my test example! But how can I do it if my example query doesn't return just one row but more than one? As far as I can see if I GROUP BY the "field_name" only one row will be returned for each number in the list. May 12, 2014 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.