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 availablea 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: 32
    • 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?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2