0

so i have this tables

log_in

+---------+------------+---------+
| date_id | date_today | user_id |
+---------+------------+---------+
|    1    | 2017/03/19 |    16   |
|    2    | 2017/03/20 |    17   |
|    3    | 2017/03/20 |    12   |
|    4    | 2017/03/21 |    16   |
|    5    | 2017/03/22 |    10   |
|    6    | 2017/03/22 |    11   |
+---------+------------+---------+

file_downloads

+---------+------------+---------+
| date_id | date_today | user_id |
+---------+------------+---------+
|    1    | 2017/03/20 |    17   |
|    2    | 2017/03/20 |    17   |
|    3    | 2017/03/20 |    12   |
|    4    | 2017/03/20 |    17   |
|    5    | 2017/03/20 |    12   |
|    6    | 2017/03/20 |    12   |
|    7    | 2017/03/20 |    12   |
|    8    | 2017/03/20 |    12   |
|    9    | 2017/03/22 |    10   |
|    10   | 2017/03/22 |    10   |
|    11   | 2017/03/22 |    11   |
+---------+------------+---------+

and this is my desired result:

+------------+-----------+--------+
| date_today | login_num | dl_num |
+------------+---------+----------+
| 2017/03/19 |     1     |   0    |
| 2017/03/20 |     2     |   8    |
| 2017/03/21 |     1     |   0    |
| 2017/03/22 |     2     |   3    |
+------------+-----------+--------+

i am still new to mysql so any help would be much appreciated. thank you! :)

miken32
  • 42,008
  • 16
  • 111
  • 154
nobody
  • 3
  • 1
  • 1
    I'll help with code you have written. –  Mar 23 '17 at 02:30
  • 1
    Write one subquery that calculates `login_num`, another subquery that calculates `dl_num`. These should both use `COUNT(*)` and `GROUP BY`. Use `LEFT JOIN` to join them. You may also want to see http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe – Barmar Mar 23 '17 at 02:32
  • Why are the fields named `dateId`, when clearly they are not identifiers for the dates? – Charles Bretana Mar 23 '17 at 02:38
  • @Barmar, thanks! will try. – nobody Mar 23 '17 at 02:46

1 Answers1

0

I think the easiest way is union all and group by:

select date_today, sum(login) as logins, sum(dl) as dls
from ((select date_today, 1 as login, 0 as dl from log_in
      ) union all
      (select date_today, 0, 1 from file_downloads
      )
     ) lfd
group by date_today;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786