Postgresql count data based on group data of another table resulting to JSON

Question

I have two tables that look like this:

users
id     name     city           state
1      Bob      Los Angeles    California
2      Susan    Dallas         Texas
3      Joe      Los Angeles    California


activity       
type           user_id
Activation     1
Registration   1
Activation     2
Activation     3

I want to count the number of times the activity of type Activation has taken place to return a dataset like this:

[
  {count: 2, city: 'Los Angeles', state: 'California'},
  {count: 1, city: 'Dallas', state: 'Texas'}
]

Here is a query I tried:

SELECT COUNT(*) AS count, user_id,
(SELECT json_agg(users.*) FROM users WHERE activity.user_id = users.id) as users
FROM activity
WHERE type='Activation'
GROUP BY users.city, users.state, user_id

And I get this error: error: missing FROM-clause entry for table "users"

I'm not sure how to do a count like this grouping data from multiple tables.

This is the latest version of Postgresql

pirho · Accepted Answer · 2018-11-02 17:44:36Z

1

I would first create a view providing correct data, like:

CREATE VIEW activations_on_locations AS
    SELECT count(*), U.city, U.state
    FROM users U
    INNER JOIN activity A
        ON U.id = A.user_id
        AND A.type='Activation'
    GROUP BY U.city, U.state;

Against that the JSON query is quite straightforward:

SELECT json_agg(AOL) FROM activations_on_locations AOL;

answered Nov 2, 2018 at 17:44

pirho

12.4k13 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jordash Over a year ago

Works great! Thanks

Heiko Jakubzik · Accepted Answer · 2018-11-02 17:46:50Z

1

You can do this to get the desired output:

SELECT json_agg(users.*) from (select 
 COUNT(id) count, min(city) city, min(state) state from users, activity
where users.id=activity.user_id group by id, city, state) as users;

answered Nov 2, 2018 at 17:46

Heiko Jakubzik

4196 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgresql count data based on group data of another table resulting to JSON

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related