# Choice of field from one table and COUNT() from another

• Good night. There's a question, and I want to solve it more elegantly.

2 tables available`a` and `b`

`````` a.id | a.name         b.id | b.aid | b.use
------+--------       ------+-------+-------
1  | Petya            1  |   1   |   0
2  | Vanya            2  |   2   |   1
3  |   1   |   0
4  |   1   |   1
5  |   2   |   0
6  |   1   |   1
7  |   1   |   0
``````

Outside, you need to get:

`````` a.id | a.name | count_total | count_busy
------+--------+-------------+------------
1  | Petya  |      5      |     2
2  | Vanya  |      2      |     1
``````

where:

• `count_total` - number of elements c `a.id = b.aid`
• `count_busy` - number of elements c `a.id = b.aid и b.use = 1`

Could this task be carried out by one request? How do you do that?

• For https://ru.stackoverflow.com/users/32625/yaant : corrected the seal: `3``2`
• For https://ru.stackoverflow.com/users/6117/yura-ivanov I haven't tried in practice yet, but the first thing that comes to mind is the requests made.

• ``````Select
a.id,
a.name,
count(*) as count_total,
sum(b.use) as count_busy
from
a,b
where
a.id = b.aid
group by
a.id, a.name;
``````

Is that right?

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2