Mysql count group by multiple tables

Question

Hi I am looking Mysql query to show count as group from two tables

Table 1 = plant

P_id    Plant location  Employee
1         DUBAI         Employee A
2         SHARJAH       Employee A
3         OMAN          Employee B
4         DAMMAM        Employee C
5         RIYADH        Employee C

Table 2 = visits

V_id    Visit status    Plant location  Employee
1        Done            DUBAI           Employee A
2        Done            SHARJAH         Employee A
3        Done            OMAN            Employee B
4        Done            RIYADH          Employee C

I need Result as

Employee    Visit Completed Remaining Visits    Result
Employee A       2             0                100 %
Employee B       1             0                100 %
Employee C       1             1                50%

SELECT employee_name,COUNT(v_id)
FROM visit      
GROUP BY employee_name;

I used this above code. It shows only visit counts. But how I can get above result table

There are a few inconsistencies in this question. Firstly you have not explained the result field, I assume it is calculated from 'Visit status' and count(V_id). If this is the case, you need to include this in your question. Also, as there are 2 tables there must be a join in the SELECT statement e.g. ...FROM visits A JOIN plant B ON A.Employee=B.Employee... Please add more details to your question. — Clinton
– Clinton, Commented Oct 9, 2019 at 22:11

nbk · Accepted Answer · 2019-10-09 21:43:17Z

With this statement

SELECT
   Employee,
   count_plant `Visit Completed` 
   ,(count_plant -count_visits)`Remaining Visits`
   , CONCAT(ROUND(((count_visits / count_plant) * 100),1), " %") Result
FROM
  (SELECT 
    p.Employee,
   COUNT(p.`Plant location`) count_plant
   ,COUNT(v.`Plant location`) count_visits
  FROM
    (SELECT
     * FROM plant) p
    LEFT join
      (SELECT *
       FROM visits 
       WHERE `Visit status` =  'Done' ) v
       ON p.Employee = v.Employee 
          and p.`Plant location` = v.`Plant location`
 GROUP BY p.Employee) p_v;

Gives following result

Employee    Visit Completed     Remaining Visits    Result
Employee A  2                   0                   100.0 %
Employee B  1                   0                   100.0 %
Employee C  2                   1                   50.0 %

The result column is rounded to i digit after the comma. You should see if that is what you need.

Collectives™ on Stack Overflow

Mysql count group by multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related