How to join SQL query in sqlite?

Question

How can I write second query instead of 'users_statistic' in first query?

SELECT * 
from 'users' LEFT JOIN 'users_statistic' 
ON users.id=users_statistic.user_id

SELECT *, SUM(clicks) as sum_clicks, SUM(page_views) as sum_page_views 
FROM 'users_statistic' 
GROUP BY user_id

Second query return table
user_id    sum_clicks       sum_page_clicks
1                 754                         543
2                 234                          987

In table 'users' is field id, which equivalent user_id in table 'users_statistic'. Also there are no fields sum_clicks and sum_page_clicks in table 'users'. I want to add fields sum_clicks and sum_page_clicks from second query to table 'users'

Answer 1

You can do the aggregation directly in the 1st query:

SELECT u.id, u.name,
       COALESCE(SUM(s.clicks), 0) sum_clicks, 
       COALESCE(SUM(s.page_views), 0) sum_page_views
FROM users u LEFT JOIN users_statistic s 
ON u.id = s.user_id
GROUP BY u.id

I use COALESCE() so that you get 0 instead of null if for a user there are no clicks or page views.