How do I get sum of two different columns from two diffrent tables and group them

user_7275462 0
Viewed 69

I have two tables which I wanna perform join between two tables and group them by date

Table one is fb_data

id  name created_at           revenue
1   as   2020-05-29 19:30:54  5
2   as   2020-05-28 19:30:54  10
3   ad   2020-05-29 19:31:42  6     
4  ad    2020-05-28 19:31:54 65
5  as    2020-05-29 19:32:10 7
6  ad    2020-05-28 19:31:54 5

Table two is google_data

id name   created_at           sessions
1  as     2020-05-29 19:38:32    5 
2  ad     2020-05-29 19:38:41    10
3  as      2020-05-29 19:38:49   7 
4  as      2020-05-28 19:39:02   6
5  ad     2020-05-28 19:39:13   8
6  ad    2020-05-28 19:41:41   13

Now what I want is to join two columns by name then group them by created_at . Something like this

Name , date(created_at) as date , sum(fb_data.revenue) , sum(google_data.revenue)

I have one sql query but that doesn't gives result as I expected 

select gd.name,fb.total_revenue,fb.created_at,gd.total_sessions
from (select date(fb_data.created_at) as created_at, sum(fb_data.revenue) as total_revenue, name
      from fb_data
      group by date(fb_data.created_at), name) fb,
     (select date(google_data.created_at) as created_at, sum(google_data.session) as total_sessions, name
      from google_data
      group by name, date(google_data.created_at)) as gd
      where fb.name = gd.name
      group by  gd.name,fb.name,fb.created_at,gd.total_sessions

The result I get is 

Name total_revenue   created_at     total_sessions
as   12              2020-05-29     12             
as   12              2020-05-29     6
as   10              2020-05-28     12
as   10              2020-05-28     6    
ad   6               2020-05-29     10
ad   6              2020-05-29      21
ad  70             2020-05-28       10
ad  70             2020-05-28      21

The result that I am expecting is

name  total_revenue   created_at total_sessions
as    12              2020-05-29   12 
as    10              2020-05-28   6
ad    6               2020-05-29   10
ad    70              2020-05-28   21
aggregate-functions mysql sql
4 Answers

Please use below query,

select gd.name,fb.total_revenue,fb.created_at, max(gd.total_sessions)
from (select date(fb_data.created_at) as created_at, sum(fb_data.revenue) as 
total_revenue, name
  from fb_data
  group by date(fb_data.created_at), name) fb,
 (select date(google_data.created_at) as created_at, sum(google_data.session) as 
total_sessions, name
  from google_data
  group by name, date(google_data.created_at)) as gd
  where fb.name = gd.name
  group by  gd.name,fb.name,fb.created_at;
user_44736150

This problem is a little harder than it looks at first glance.

You'll need to join three subqueries, each with one row per name and date.

The first subquery gives you all the possible names and dates. You need this one because one of your two detail file might be missing some name/date combinations the other one contains

        SELECT DISTINCT name, created_at
          FROM (
             SELECT name, DATE(created_at) created_at FROM fb
              UNION
             SELECT name, DATE(created_at) created_at  FROM gg
            ) a

Your second subquery gets session counts for each name / date.

             SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(sessions) sessions
               FROM gg
              GROUP BY name, DATE(created_at)

The third one does the same for revenue.

             SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(revenue) revenue
               FROM fb
              GROUP BY name, DATE(created_at)

You then LEFT JOIN the second and third subqueries to the first subquery.

SELECT items.name, items.created_at, sess.sessions, rev.revenue
  FROM (first subquery) items
  LEFT JOIN (second subquery) sess ON items.name = sess.name
                                 AND items.created_at = sess.created_at
  LEFT JOIN (third subquery) rev ON items.name = rev.name
                                AND items.created_at = rev.created_at

All together, you get this.

SELECT items.name, items.created_at, sess.sessions, rev.revenue
  FROM (SELECT DISTINCT name, created_at
          FROM (
             SELECT name, DATE(created_at) created_at FROM fb
              UNION
             SELECT name, DATE(created_at) created_at  FROM gg
            ) a
       ) items
  LEFT JOIN (SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(sessions) sessions
               FROM gg
              GROUP BY name, DATE(created_at)
            ) sess ON items.name = sess.name
                  AND items.created_at = sess.created_at
  LEFT JOIN (SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(revenue) revenue
               FROM fb
              GROUP BY name, DATE(created_at)
            ) rev ON items.name = rev.name
                  AND items.created_at = rev.created_at

https://www.db-fiddle.com/f/hiAKaSDWn1FuUufF3oLbgZ/0

user_2056080

Try the following query:

SELECT t.name, t.total_revenue, t.created_at, t2.total_sessions
FROM
  (SELECT name, date(created_at) created_at, sum(revenue) total_revenue
   FROM fb_data
   GROUP BY name,
            date(created_at)) t
INNER JOIN
  (SELECT name, date(created_at) created_at, sum(sessions) total_sessions
   FROM google_data
   GROUP BY name,
            date(created_at)) t2 ON t2.created_at = t.created_at
AND t2.name = t.name;
user_4074120

You can use below simple Query. It gives the desired result :

select aa.name,aa.created, sum(ses), sum(rev)  
from 
    (select fb_data.name, date(created_at) as created, sum(revenue) as rev, 0 as ses
    from fb_data group by fb_data.name,date(created_at) 
    UNION
    select google_data.name, date(created_at) as created, 0 as rev, sum(sessions) as ses
    from google_data group by google_data.name,date(created_at)
    ) as aa 
group by name,created;

Check Results : https://www.db-fiddle.com/f/7wpGKon7SH58HNF7Q4SuFH/1

user_104782680
Related