MySQL获得每个分支的查询总数

问题描述：

I have three tables and they are the following

User Table

+---------+-----------+--------+
| user_id | user_name | branch |
+---------+-----------+--------+
| 1       | John      | 1      |
| 2       | Jim       | 2      |
| 3       | Jern      | 3      |
| 4       | Jack      | 1      |
| 5       | Jery      | 2      |
| 6       | Tom       | 3      |
| 7       | Sona      | 1      |
| 8       | Tina      | 3      |   
+---------+-----------+--------+

Branch Table

+-----------+----------------+
| branch_id | branch_name    |
+-----------+----------------+
| 1         | IT             |
| 2         | SALES          |
| 3         | Administration |
+-----------+----------------+

Enquiry Table

+------------+---------------+---------+
| enquiry_id | enquiry_name  | user_id |
+------------+---------------+---------+
| 1          | enqury_test1  | 1       |
| 2          | enqury_test2  | 2       |
| 3          | enqury_test3  | 1       |
| 4          | enqury_test4  | 3       |
| 5          | enqury_test5  | 2       |
| 6          | enqury_test6  | 5       |
| 7          | enqury_test7  | 1       |
| 8          | enqury_test8  | 2       |
| 9          | enqury_test9  | 4       |
| 10         | enqury_test10 | 6       |
| 11         | enqury_test11 | 2       |
| 12         | enqury_test12 | 7       |
+------------+---------------+---------+

From the above tables its clear that, each branch contains a number of users. These users post multiple enquiries. I need to get the total number of enquiries in each branch as

branch id => number of enquiries

I have tried various queries. But i couldn't get the result. Any one can help? I am using MySQL and i need a single query to do this.

Thanks in advance

答

You need count and group by

select
b.branch_id,
count(e.user_id) as `total_enq`
from Branch b
left join User u on u.branch = b.branch_id
left join Enquiry e on e.user_id = u.user_id
group by b.branch_id

答

The query you have to perform to get you desired result is like this :-

$query = "SELECT u.branch, COUNT(u.user_id) AS `total_enquires` 
          FROM enquiry e INNER JOIN user u ON e.user_id = u.user_id
          GROUP BY u.branch"

This will help you,and i think you don't need to join branch table as user table already contain branch_id.

答

SELECT 
        bt.branch_id
        ,COUNT(enquiry_id) AS total_enquiry
FROM 
    enquiry_table et
    INNER JOIN user_table ut on ut.user_id = et.user_id
    INNER JOIN branch_table bt ON bt.branch_id = ut.branch
WHERE  
    1=1
GROUP BY
        bt.branch_id

you can try this

答

This is the query

SELECT `branch`,`branch_name`,count(`user`.`user_id`),count(`enquiry_id`) FROM `user` inner join `branch` on `user`.`branch`=`branch`.`branch_id` inner join `enquiry` on `user`.`user_id`=`enquiry`.`user_id` group by `branch`

try it here http://sqlfiddle.com/#!9/cf3eb/1

MySQL获得每个分支的查询总数

相关推荐