Take the 2-minute tour ×
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.

Basically I have a list of numbers like:

1,2,3,4,5

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.

-----EDIT-----

What if the query to execute would be something like:

SELECT *
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 at 12:54
    
@Michael, Thanks for the suggestion, I didn't know about this. –  Fabio Spampinato May 12 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 ; –  ypercube May 12 at 13:05

1 Answer 1

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 at 11:20
    
GROUP BY .... –  ypercube May 12 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 at 11:21
    
Sorry I forgot the GROUP BY field_name. That should work –  James Anderson May 12 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 at 11:50

Your Answer

 
discard

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.