CODEWITHSUNDEEP
sqlpostgresqlsum
gerpaick
gerpaick

Reputation: 799

how to sql query 2 tables and group by?

i have 2 tables: activities and users. users has columns: name, active activities: name, type, time, user_id.

for example i have these tables:

users
-----
id | name | active
1  | marc | true
2  | john | true
3  | mary | true
4  | nico | true

activities
-----
id | name | type | time | user_id
1  | morn | walk | 90   | 2
2  | morn | walk | 22   | 2
3  | morn | run  | 12   | 2
4  | sat  | walk | 22   | 1
5  | morn | run  | 13   | 1
6  | mond | walk | 22   | 3
7  | morn | walk | 22   | 2
8  | even | run  | 42   | 1
9  | morn | walk | 22   | 3
10 | morn | walk | 62   | 1
11 | morn | run  | 22   | 3

now i would like to get table that would sum time spent on each type of activity and would group it by user name. so:

result
------
user name | type | time
marc      | walk | 84
marc      | run  | 55
john      | walk | 134
john      | run  | 12
mary      | walk | 44
mary      | run  | 2
nico      | walk | 0
nico      | run  | 0

how should i write this query to get this result?

thanks in advance gerard

Upvotes: 1

Views: 84

Answers (3)

roman
roman

Reputation: 117571

you can use coalesce to get 0 for empty activities and distinct to get all type of possible activities

select
   u.name, c.type,
   coalesce(sum(a.time), 0) as time
from (select distinct type from activities) as c
   cross join users as u
   left outer join activities as a on a.user_id = u.id and a.type = c.type
group by u.name, c.type
order by u.name, c.type

sql fiddle demo

Upvotes: 1

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

Select u.name, a.type, SUM(a.time) FROM 
activities a
LEFT JOIN users u
ON a.user_id = u.id
GROUP BY u.name, a.type

FIDDLE

Use this to get zero count as well

SELECT c.name,c.type,aa.time FROM
(Select u.id,u.name, b.type  FROM 
users u
CROSS JOIN (SELECT DISTINCT type FROM activities) b) c
LEFT JOIN (SELECT a.user_id, a.type, SUM(a.time) as time FROM
activities a 
GROUP BY a.user_id, a.type) aa ON
aa.user_id = c.id and c.type = aa.type

Fiddle2

Upvotes: 1

Philippe T.
Philippe T.

Reputation: 1192

this might work :

select users.name,activities.type,sum(activities.time)
from users left join activities on users.id = activities.user_id
where users.active group by users.name,activities.type

Upvotes: 0

Related Questions