sardapv sardapv - 2 months ago 11
MySQL Question

How to calculate individual sum of selected column

i am currently working on university management project where i want to calculate capacity of each building when the case is each department can hold different sections like below (comp and elect in same building)
i wrote a query where i am getting total sum of capacity which is unwanted
my query is:

select sum(distinct capacity)
from classroom
where building in (select building from department group by building)


i am getting ans as 660 !!
am I using sum and distinct at wrong places in this nested query? how to get capacity of individual building??

department
+------------+----------+-----------+
| dept_name | building | budget |
+------------+----------+-----------+
| Biology | Watson | 90000.00 |
| Comp. Sci. | Taylor | 100000.00 |
| Elec. Eng. | Taylor | 85000.00 |
| Finance | Painter | 120000.00 |
| History | Painter | 50000.00 |
| Music | Packard | 80000.00 |
| Physics | Watson | 70000.00 |
+------------+----------+-----------+
classroom
+----------+-------------+----------+
| building | room_number | capacity |
+----------+-------------+----------+
| Packard | 101 | 500 |
| Painter | 514 | 10 |
| Taylor | 3128 | 70 |
| Watson | 100 | 30 |
| Watson | 120 | 50 |
+----------+-------------+----------+

Answer

Since you want to check only capacities of buildings that exist in department table you need for each building to calculate a sum over capacity and add exists clause to show only those buildings that are residences for at least one apartment:

select
  building, sum(capacity) as capacity
from classrom c
where exists (
  select 1
  from department d
  where c.building = d.building
  )
group by building

If you only need capacities regardless of building existence in department then drop the exists clause:

select
  building, sum(capacity) as capacity
from classrom
group by building
Comments