CODEWITHSUNDEEP
sql
Minchae
Minchae

Reputation: 83

How to subtract two columns in different table

I have a table of ward

ward_number | class | capacity
________________________________________
1           | A1    | 1
2           | A1    | 2
3           | B1    | 3
4           | C     | 4
5           | B2    | 5

capacity = how many beds there is in the ward

I also have a table called ward_stay:

ward_number | from_date   | to_date
_____________________________________________
2           | 2015-01-01  | 2015-03-08
3           | 2015-01-16  | 2015-02-18
6           | 2015-03-05  | 2015-03-18
3           | 2015-04-15  | 2015-04-20
1           | 2015-05-19  | 2015-05-30

I want to count the number of beds available in ward with class 'B1' on date '2015-04-15':

ward_number  | count 
_____________________
  3          | 2

How to get the count is basically capacity - the number of times ward_number 3 appears

I managed to get the number of times ward_number 3 appears but I don't know how to subtract capacity from this result.

Here's my code:

select count(ward_number) AS 'result'
from ward_stay
where ward_number = (select ward_number
                     from ward 
                     where class = 'B1');

How do I subtract capacity from this result?

Upvotes: 0

Views: 81

Answers (3)

dnoeth
dnoeth

Reputation: 60462

select w.ward_number,
   w.capacity - count(ws.ward_number) AS "result" 
from ward as w left join ward_stay as ws 
on ws.ward_number = w.ward_number
and date '2015-05-19' between ws.from_date and ws.to_date
where w.class = 'B1' -- which class
     -- bed not occupied on that date
group by w.ward_number, w.capacity
having w.capacity - count(*) > 0 -- only available wards

See fiddle

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

SQL Fiddle Demo

Using 2015-01-17 instead I calculate the total of occupied bed on that day. Then join back to substract from original capacity. in case all bed are free the LEFT JOIN will return NULL, so COALESCE will put 0

SELECT w."ward_number", "capacity" - COALESCE(occupied, 0) as "count"
FROM wards w
LEFT JOIN (
      SELECT "ward_number", COUNT(*) occupied
      FROM ward_stay
      WHERE to_date('2015-01-17', 'yyyy-mm-dd') BETWEEN "from_date" and "to_date"
      GROUP BY "ward_number"
     ) o     
  ON w."ward_number" = o."ward_number"
WHERE w."class" = 'B1'

OUTPUT

| ward_number | count |
|-------------|-------|
|           3 |     2 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269523

You need to aggregate both tables before returning, because you have multiple rows for the same word type in both. So:

select c.class, (c.capacity - coalesce(wc.occupied)) as available
from (select class, sum(capacity) as capacity
      from ward
      group by class
     ) c left join
     (select w.class, count(*) as occupied
      from ward_stay ws join
           ward s
      on ws.ward_number = w.ward_number and
         '2015-05-19' between ws.from_date and ws.to_date
     ) wc
     on w.class = wc.class;

Note: this is standard SQL except for the date constant. This works in most databases; some might have other formats (or it might depend on internationalization settings).

Strictly speaking the aggregation on ward is not necessary for "B1". But it is clearly necessary for "A1".

Upvotes: 0

Related Questions