CODEWITHSUNDEEP
jsonpostgresql
Jordash
Jordash

Reputation: 3093

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

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

Upvotes: 0

Views: 45

Answers (2)

Heiko Jakubzik
Heiko Jakubzik

Reputation: 419

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;

Upvotes: 1

pirho
pirho

Reputation: 12215

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;

Upvotes: 1

Related Questions