sardapv - 1 year ago 106
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 |
+----------+-------------+----------+
``````

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download