EP82

Is it possible to add two figures up that are in the same column as each other?

For instance;

select department, count(*)
from table
where deparment in ('55', '60')
group by department

I now have two rows returned one with a count of how many rows are in department 55 another with a count of how many rows are in department 60.

How do i now add those two figures together?

Any help would be great.

Cheers

Sub97

select count(*)
from table
where department in ('55, '60')

EP82

Infact i worded that wrong, lol..

I need to subtract the two figures.

Kevin Greeley

If using Access, you could use:

SELECT SUM(SWITCH(Department=55,1,Department=60,-1))
FROM Table;

For Oracle, change to DECODE function.
For others, maybe the CASE function.

Dracoro

SELECT
COUNT(CASE WHEN department = '55' THEN 1 ELSE 0),
COUNT(CASE WHEN department = '60' THEN 1 ELSE 0),
COUNT(CASE WHEN department = '55' THEN 1 ELSE 0) - COUNT(CASE WHEN department = '60' THEN 1 ELSE 0),
COUNT(CASE WHEN department = '60' THEN 1 ELSE 0) - COUNT(CASE WHEN department = '55' THEN 1 ELSE 0)
FROM table

Gives you count of 55, count of 60, count of 55s - 60s and count of 60s - 55s