0

I have this query:

String queryStr = 'SELECT COUNT(*) FROM tester x WHERE x.state !=:state'
setParameter("state", "state1");

I want to count all that are "state1" and "state2" Is there a way to do it rather than :

String queryStr = 'SELECT COUNT(*) FROM tester x WHERE (x.state !=:state1 AND x.state !=:state2)'; 
setParameter("state1", "state1");
setParameter("state2", "state2");

Thanks

bf2020
  • 742
  • 4
  • 7
user1386966
  • 3,302
  • 13
  • 43
  • 72
  • What are the other columns in the table, you can do it using `group by` – Abhik Chakraborty Apr 15 '15 at 11:06
  • It is not clear if you want to count the lines which state equals state1 and states2 separatly. And the second query counts the lines that ar not equals. Of course the state cannot be equal to state1 AND state2? – pdem Apr 15 '15 at 11:21
  • 1
    yes @pdem now you understand why i have written two answers? – Imran Qamer Apr 15 '15 at 11:22
  • @Imran Qamer Yes i do. – pdem Apr 15 '15 at 11:25
  • Edit your query and add sample data and desired results. The question doesn't make sense. – Gordon Linoff Apr 15 '15 at 11:55
  • you can use `NOT(NOT(cond1) OR NOT(cond2))` instead of `cond1 AND cond2` :-) just kidding, your select does exactly the opposite of what you describe, use A) `WHERE state IN (:state1, :state2)` or B) `WHERE state NOT IN (:state1, :state2)`; when you want the counts per state, then `SELECT COUNT(*), state FROM ... WHERE ... GROUP BY state` – Pavel Gatnar Apr 15 '15 at 14:20
  • Are you asking how to associate a parameter with more than one value, something like `state not in (:states)` ? As far as I know that does not exist. But then I only worked with Sybase. Oracle or MS SQL may have something like that... Barring that, use a temporary table and fill it with the values you need excluded. Update: http://stackoverflow.com/questions/4624398/passing-multiple-values-for-one-sql-parameter –  Apr 15 '15 at 16:36

1 Answers1

0

use not in

WHERE (x.state NOt IN (:state1,:state2))

then your whole query will be 

'SELECT COUNT(*) FROM tester x WHERE (x.state NOt IN (:state1,:state2))';

Note: above solution is according to yor query, but according to your text [I want to count all that are "state1" and "state2"]

then your query can be

'SELECT COUNT(*) FROM tester x WHERE x.state = :state1 AND x.state = :state2';
Imran Qamer
  • 2,253
  • 3
  • 29
  • 52
  • The first query is right. I don't understand why you added the second query that will always return 0. By the way the requirement ask for it but it has no sense. – pdem Apr 15 '15 at 11:16
  • actually question is a little bit confusing just re study this line [I want to count all that are "state1" and "state2"] – Imran Qamer Apr 15 '15 at 11:17