PostgreSQL 12.9: Finding the count of other players that participated in the same activity-CodePudding

Version PostgreSQL 12.9

I have the following table that catalogs a list of activities played by a playerid, and another table that serves as the map for the players information along with a clan id, what I want to do is count the number of players that participated in the same instance id, that have the same clan id. My table structure is as follows:

Activities Table

CREATE TABLE "public"."activities" (
  "playerdestinyid" int8 NOT NULL,
  "instanceid" int8 NOT NULL,
  "timestamp" timestamptz(6) NOT NULL);

Players Table

CREATE TABLE "public"."players" (
  "destinyid" int8 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "clanid" int8 NOT NULL,
  "characterids" jsonb NOT NULL);

Edit 1: First attempted query

SELECT
    ROUND(
        SUM (
            ( ( activities.kills * activities.killsdeathsratio ) * 100 ) * ( SELECT COUNT ( * ) * 1.05 FROM "activities" INNER JOIN "players" ON ( "playerdestinyid" = "destinyid" ) GROUP BY "instanceid", "clanid" ) 
        )) AS "Score",
        players.clanid AS "clanId",
        ROW_NUMBER ( ) OVER ( ORDER BY SUM ( ( activities.kills * activities.killsdeathsratio ) * 100 ) DESC ) AS POSITION,
        COUNT ( players.clanid ) AS "gamesplayed" 
    FROM
        activities
        INNER JOIN players ON activities.playerdestinyid = players.destinyid
    WHERE
        TIMESTAMP BETWEEN '2022-03-15 17:00:00 00' 
        AND now( ) 
    GROUP BY
        players.clanid
ORDER BY
    SUM ( ( activities.kills * activities.killsdeathsratio ) * 100 ) DESC

The error returned:

> ERROR:  more than one row returned by a subquery used as an expression

Edit 2: I got what the query re-written using the CTE as suggested, but it's still giving me the ERROR: more than one row returned by a subquery used as an expression response though.

WITH clan_multi AS ( SELECT (COUNT ( * ) * 1.05) as multiplier FROM "activities" INNER JOIN "players" ON ( "playerdestinyid" = "destinyid" ) GROUP BY "instanceid", "clanid" ) 
SELECT
ROUND( SUM ( ( activities.kills * activities.killsdeathsratio ) * 100 ) ) AS "Score",
players.clanid AS "clanId",
(SELECT multiplier from clan_multi),
ROW_NUMBER ( ) OVER ( ORDER BY SUM ( ( activities.kills * activities.killsdeathsratio ) * 100 ) DESC ) AS POSITION,
COUNT ( players.clanid ) AS "gamesplayed" 
FROM
    activities
    INNER JOIN players ON activities.playerdestinyid = players.destinyid 
    INNER JOIN clan_multi ON activities.instanceid = instanceid AND players.clanid = clanId
WHERE
    TIMESTAMP BETWEEN '2022-03-15 17:00:00 00' 
    AND now( ) 
GROUP BY
    players.clanid 
ORDER BY
    SUM ( ( activities.kills * activities.killsdeathsratio ) * 100 ) DESC

CodePudding user response：

SELECT
  "instanceid",
  "clanid",
  count(*)
FROM "activities"
INNER JOIN "players" ON ("playerdestinyid" = "destinyid")
GROUP BY
  "instanceid",
  "clanid"