CODEWITHSUNDEEP
mysqldatabasejoin
Bilal Halayqa
Bilal Halayqa

Reputation: 972

MySql update table from another one join and group by

If I have table logs :

ID   Date     P_id     TYPE
-------------------------------
1   2016-9-1   11    adClick       
2   2016-9-1   22   adComplete
3   2016-9-1   11   adComplete
4   2016-9-3   22    adClick        
5   2016-9-3   22    adClick        
6   2016-9-1   44    adClick        
7   2016-9-3   44    adComplete        
8   2016-9-3   44    adClick        
9   2016-9-3   11    adClick        
-------------------------------

and another table report having the same Date & P_id as follows :

ID   Date     P_id     clicks   
--------------------------------
1   2016-9-1   11      
2   2016-9-1   11      
3   2016-9-1   22        
4   2016-9-3   22      
5   2016-9-1   11      
6   2016-9-1   44           
5   2016-9-1   44      
6   2016-9-1   11           
---------------------------------

I need MySQL query to fill clicks in report table and according to the key (Date & P_id) :

clicks = 
    count of rows having (Date & P_id) in Report table 
          divided by 
    count of rows having (Date & P_id) and Type is adClick

So the table will be :

ID   Date     P_id     clicks   
--------------------------------
1   2016-9-1   11      4 / 1
2   2016-9-1   11      4 / 1
3   2016-9-1   22        0
4   2016-9-3   22      2 / 2
5   2016-9-1   11      4 / 1
6   2016-9-1   44      2 / 1     
5   2016-9-1   44      2 / 1
6   2016-9-1   11      4 / 1      
---------------------------------

Sample, first row :

2016-9-1   11      4 / 1 

4 rows (2016-9-1   11) in report table by
1 row  (2016-9-1   11) in logs table with type=adClick

What I have tried so far :

UPDATE report AS r 

INNER JOIN 
(
    SELECT 
        *, count(id) AS count_value
    FROM 
        logs
    WHERE
        type= "adClick"
    GROUP BY 
        date,p_id

) log

ON  r.date=log.date AND  r.p_id=log.p_id

SET r.clicks=(log.count_value / (SELECT COUNT(lof) from report) );

Thanks in advance

Upvotes: 0

Views: 1913

Answers (3)

Bilal Halayqa
Bilal Halayqa

Reputation: 972

Thanks all, the following did the job for me :

UPDATE report AS r 
INNER JOIN 
(
    SELECT cnt_adclick,count(*) as cnt_report,date, report.P_id
    FROM report
    INNER JOIN 
    (
        SELECT date as date, P_id, SUM(event_type = 'adClick') as cnt_adclick
        FROM logs
        GROUP BY date,P_id
    ) inner_log

    ON report.date = inner_log.date AND report.P_id = inner_log.P_id
    GROUP BY report.date, report.P_id
) l

ON r.date = l.date AND r.P_id= l.P_id
SET r.clicks = cnt_adclick / cnt_report;

Upvotes: 0

Faisal
Faisal

Reputation: 4765

Try using CONCAT()

UPDATE report AS r
INNER JOIN (
    SELECT
        date,
        p_id,
        count(*) AS count,
        SUM(type = 'adClick') AS count_adclick
    FROM
        logs
    GROUP BY
        date,
        p_id
) log ON r.date = log.date
AND r.p_id = log.p_id
SET r.clicks = CONCAT(log.count_adclick, ' / ', log.count);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can do this using conditional aggregation:

UPDATE report AS r INNER JOIN 
       (SELECT date, p_id, count(*) AS cnt,
               SUM(type = 'adClick') as cnt_adclick
        FROM logs
        GROUP BY  date,p_id
       ) l
       ON r.date = l.date AND r.p_id = l.p_id
    SET r.clicks = cnt_adclick / cnt;

You can also do this with avg() instead of division:

UPDATE report AS r INNER JOIN 
       (SELECT date, p_id, 
               AVG(type = 'adClick') as avg_clicks
        FROM logs
        GROUP BY  date,p_id
       ) l
       ON r.date = l.date AND r.p_id = l.p_id
    SET r.clicks = avg_clicks;

Upvotes: 1

Related Questions