CODEWITHSUNDEEP
sqlpostgresqlaggregateaggregate-functions
luc-georges
luc-georges

Reputation: 13

How would you group multiple result in one column with a count for each result using postgreSQL

I'm in a bit of a struggle right now, I've been looking all day but I haven't managed to find out how to do what I'm supposed to do.

I would like to do a view for my API using postgres (can be a function if needed) which will show the few top players (the one who have created the most Events)

The result I'm looking for would be an output like:

{"nickname": "Username" , ... , "total_event_created": 12, "Events":{"eventID": 1 , "event_name":CS:go, "total": 6}, {"eventID: 2 , "event_name": lol, "total": 6}

First I've got my Event table like that

CREATE TABLE "event" (
"id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"user_id" INT NOT NULL REFERENCES "user"("id"),
"game_id" INT NOT NULL REFERENCES "game"("id"),
"event_time" TIMESTAMPTZ NOT NULL,
"duration" INTERVAL ,
"player_count" INT NOT NULL DEFAULT 1  CONSTRAINT max_player CHECK (player_count <= player_max),
"player_max" INT NOT NULL ,
"description" TEXT,
"status" INT NOT NULL DEFAULT 0 ,
"vocal" TEXT);

I've tried a lot of different things using json_agg() but I never manage to get something right.

So far my best try is this one:

SELECT DISTINCT ON (e.user_id) "user_id" AS "_user_id",
    us.nickname AS "_name",
    us.avatar AS "_avatar",
    us.banner AS "_banner",
    (SELECT COUNT(id) FROM user_access."event" ev WHERE us.id = ev.user_id) AS "_total_events",
    (SELECT COUNT (CASE WHEN g.id = 1 THEN 1 END) ) AS "_total_cs",
    (SELECT COUNT (CASE WHEN g.id = 2 THEN 1 END) ) AS "_total_lol"
FROM user_access."user" us
JOIN user_access."event" e  ON us.id = e.user_id 
JOIN user_access."game"  g ON  e.game_id = g.id
GROUP BY  e.user_id, us.nickname, us.id ;

output:

_user_id |   _name    |   _avatar   |   _banner   | _total_events | _total_cs | _total_lol 
----------+------------+-------------+-------------+---------------+-----------+------------
       1 | test1login | avatar1.png | banner1.png |             3 |         2 |          1
       2 | test2login | avatar2.png | banner2.png |             1 |         0 |          1

Thanks guy's for clearing my code, from what you send me i arrived to something better :

SELECT  us."id" AS "_user_id",
            us.nickname AS "_name",
            us.avatar AS "_avatar",
            us.banner AS "_banner",
            COUNT(DISTINCT e.id) as "_total_events",
             jsonb_build_object('cs', COUNT(*) FILTER (WHERE g.id = 1 ),'lol',  COUNT(*) FILTER (WHERE g.id = 2))  AS "_total_by_game"       
        FROM user_access."user" us 
        JOIN user_access."event" e ON us.id = e.user_id 
        JOIN user_access."game"  g ON e.game_id = g.id
    GROUP BY us.id
    ORDER BY "_total_events" DESC;

output:

 _user_id |   _name    |   _avatar   |   _banner   | _total_events |   _total_by_game    
 ----------+------------+-------------+-------------+---------------+---------------------
          1 | test1login | avatar1.png | banner1.png |          3 | {"cs": 2, "lol": 1}
          2 | test2login | avatar2.png | banner2.png |          1 | {"cs": 0, "lol": 1}

Upvotes: 1

Views: 66

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There are several issues in your query:

  1. You group by user. You do this thrice, for no apparent reason: Once by their ID, then by by their name, then by their ID again.
  2. You use DISTINCT ON user ID. Why? The GROUP BY already gives you one row per user. Then with DISTINCT ON you say: from this one row, give me one row, no matter which (because there is no ORDER BY clause that you'd usually have for DISTINCT ON, but well, you only have one row per user anyway, so this all is just void).
  3. (SELECT COUNT (CASE WHEN g.id = 1 THEN 1 END) ) is invalid and should raise an error. It can be re-written to CASE WHEN g.id = 1 THEN 1 ELSE 0 END. But as you are aggregating per user there can be multiple g.id. Which one do you want to show? There would have to be an aggregation function for this to work, e.g. SUM((SELECT COUNT (CASE WHEN g.id = 1 THEN 1 END))), which of course would again just be SUM(CASE WHEN g.id = 1 THEN 1 ELSE 0 END).

It seems you just want an aggregation:

SELECT
  us.id AS "_user_id",
  us.nickname AS "_name",
  us.avatar AS "_avatar",
  us.banner AS "_banner",
  COUNT(*) AS "_total_events",
  COUNT(CASE WHEN g.id = 1 THEN 1 END) AS "_total_cs",
  COUNT(CASE WHEN g.id = 2 THEN 1 END) AS "_total_lol"
FROM user_access.user us
JOIN user_access.event e ON e.user_id = us.id 
JOIN user_access.game g ON g.id = e.game_id
GROUP BY us.id

For the top 5 add:

ORDER BY COUNT(*) DESC
FETCH FIRST 5 ROWS ONLY;

BTW: Do you really want to count how many events a user had in game 1 (_total_cs) and 2 (_total_lol) or is this a typo?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use conditional aggregation:

SELECT us."user_id" AS "_user_id",
       us.nickname AS "_name",
       us.avatar AS "_avatar",
       us.banner AS "_banner",
       COUNT(DISTINCT e.id) as "_total_events",
       COUNT(*) FILTER (WHERE g.id = 1 ) AS "_total_cs",
       COUNT(*) FILTER (WHERE g.id = 2) AS "_total_lol"
FROM user_access."user" us JOIN
     user_access."event" e
     ON us.id = e.user_id JOIN
     user_access."game"  g
     ON e.game_id = g.id
GROUP BY us.id ;

Upvotes: 1

Related Questions