double filters with count(*)

[gardan06]

i have this table:

 

PR #PR # includedSub Dept Code

A(NULL)1

B(NULL)5

C(NULL)2

D(NULL)4

E(NULL)7

F(NULL)9

Merged1A*B*C*D*E*F(NULL)

i merged PRs A to F in Merged1.

 

my problem is that i want to know which rows from A to F belong to sub_dept_code 1-5, then the other from 6-9, so that will make 2 count(*) queries right?

 

my current query right now (which works by the way) is:

select * from 
(select count(*) as total1 from PR where PRNo in ('A','B','C','D','E','F') and sub_dept_code in ('1','2','3','4','5')) as z,
(select count(*) as total2 from PR where PRNo in ('A','B','C','D','E','F') and sub_dept_code in ('6','7','8','9')) as y

 

..but i feel there's a better way to code it. is there a better and more optimal way to translate my query, or is this it?

[gardan06]

anyone?

[gardan06]

someone out there has got to have a solution to this...

[fenway]

That depends... why not:

 

select
SUM( IF( sub_dept_code BETWEEN 1 AND 5, 1, 0 ) as total1
,SUM( IF( sub_dept_code BETWEEN 6 AND 9, 1, 0 ) as total2
from PR
where PRNo BETWEEN 'A' and 'F'