Good time of day. The question has ripened, and I want to solve it more elegantly.

There are 2 tables ( 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

At the exit you need to get:

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

Where:

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

Is it possible to perform this task in one request? How to do it better?

  • For @Yaant : corrected typo: 3 by 2 .
  • For @Yura Ivanov : I haven't tried it in practice yet, but the first thing that comes to mind is nested queries.
  • What options have you tried? - Yura Ivanov

2 answers 2

 SELECT a.id , a.name , count(*) as count_total , sum(case when b.use > 0 then 1 else 0 end) as count_busy FROM a inner join b on a.id = b.aid GROUP BY a.id, a.name;

Like so

     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 suitable?

    • My fault is that I did not indicate it right away, but use is not boolean. use may be 0-999 - Vasily Medvedev
    • @pavel Use ANSI join, see label description [sql] - Yura Ivanov
    • @YuraIvanov and what is join better than the condition in where ? - pavel
    • My comment is not for the sake of discussion, but please adhere to the uniformity in the answers to sql, which, as I said, is written in the label description. If you are interested in "better" or not "better", ask a separate question or look for information yourself, for example, see here stackoverflow.com/questions/1599050/… - Yura Ivanov