Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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. – Michael Green May 12 '14 at 12:54
@Michael, Thanks for the suggestion, I didn't know about this. – Fabio Spampinato May 12 '14 at 12:59
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 ; – ypercubeᵀᴹ May 12 '14 at 13:05
up vote 2 down vote accepted

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
share|improve this answer
Actually I can't, it produces different results. In my test case all the "COUNT(*)"s were summed for example. – Fabio Spampinato May 12 '14 at 11:20
GROUP BY .... – ypercubeᵀᴹ May 12 '14 at 11:21
Add "GROUP BY field_name" to the above example and you should get the result you desire. – Dag Sondre Hansen May 12 '14 at 11:21
Sorry I forgot the GROUP BY field_name. That should work – James Anderson May 12 '14 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. – Fabio Spampinato May 12 '14 at 11:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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