Finding number of passwords left

[jerijeri]

HI,

We have a table that looks like the following:

id,username,password,area,order_id

1,joe,88,5,100
2,joex,99,5,102
3,joey,77,5,NONE
4,jane,66,4,101
5,janey,22,4,NONE

What we want to do is have the results displayed as

Area, Available, Issued

5 1 2
4 1 1

Originally we had 2 tables. The SQL statements were

SELECT area, COUNT(*) as cnt_used FROM table_name WHERE order_id != 'NONE'

and

SELECT area, COUNT(*) as cnt_used FROM table_name WHERE order_id = 'NONE'

This last one doesn't display 0 and that's critical.

So we thought of combining the two statments and getting one results. We use the case statement on the order_id column and then count on the two different cases.

No luck so far. Any help appreciated.

Thanks,

Jer

 

[swampBoogie]

select area,
sum(case when order_id = 'NONE' then 1 else 0 end) as available,
sum(case when order_id = 'NONE' then 0 else 1 end) as issued
from table_name
group by area

 

[r937]

are you really storing the 4-character string 'NONE'? in a numeric field? or is that the way you display nulls?

rudy

 

[swampBoogie]

Well, I assumed that order_id was a character column. The question remains though.

 

[jerijeri]

order_id is a character column. The order ids are alphanumeric. That isn't clear in the example.

NONE was entered instead of just N or leaving as a Null, to make it clearer to the support staff that may have to edit a record. NONE makes it clearer.

select area,
sum(case when order_id = 'NONE' then 1 else 0 end) as available,
sum(case when order_id = 'NONE' then 0 else 1 end) as issued
from table_name
group by area

did the trick.
Thank you,

Jer