The warehouse exercise 8

Back to The warehouse

Select the warehouse codes, along with the number of boxes in each warehouse. Optionally, take into account that some warehouses are empty (i.e., the box count should show up as zero, instead of omitting the warehouse from the result).

Without zero count

| SELECT code, warehouse FROM boxes
| GROUP warehouse, code COUNT BY warehouse
4 rows 0 msec
warehouse code-count
1 4
2 2
3 2
4 2


With zero count is difficult to check here, because all warehouses have boxes.

We have created a second table W2 data with a sixth warehouse in Boston.

| SELECT code, location, capacity FROM w2
6 rows 0 msec
code location capacity
1 Chicago 3
2 Chicago 4
3 New York 7
4 Los Angeles 2
5 San Francisco 8
6 Boston 5


| SELECT code FROM w2
| RENAME code warehouse
| SELECT code, warehouse FROM boxes
| EXCEPT warehouse
| CALC code-count "0"
| SELECT code, warehouse FROM boxes
| GROUP warehouse, code COUNT BY warehouse
| UNION
6 rows 0 msec
warehouse code-count
5 0
6 0
1 4
2 2
3 2
4 2


Another simpler solution using the UNION statement

| SELECT code, warehouse FROM boxes
| GROUP warehouse, code COUNT BY warehouse
| SELECT code FROM w2
| RENAME code warehouse
| UNION
10 rows 0 msec
warehouse code-count
1 4
2 2
3 2
4 2
1  
2  
3  
4  
5  
6  


Note that this time, the count is empty and not 0, because the column count did not exist. If there are tuples with the same UNION field and common columns exist, the deeper in the stack has precedence.