Postgres: subquery uses ungrouped column from outer query-CodePudding

So I am trying to create a query where I group a subquery in an array.
I am not an expert in Postgres especially not arrays in queries so I am trying to learn the more advanced stuffs.

I want to reach the following output:

    Date    |             Jobs             
-----------------------------------------------------------------------------
 21/11/2022 | {{TestJob1,1500},{TestJob2,1100},{TestJob3,500}}
 20/11/2022 | {{TestJob1,1300},{TestJob2,100},{TestJob3,500}}
 19/11/2022 | {{TestJob1,1400},{TestJob2,1900}}
 18/11/2022 | {{TestJob1,1200},{TestJob2,1700},{TestJob3,800},{TestJob4,500}}

I alread started experimenting and this is how far I got:

SELECT j."Start time"::date AS "Date",
  (select array["Job name", count(*)::varchar] from amdw."Job runs" where "Start time"::date = j."Start time"::date group by "Job name") as "Jobs"
  FROM amdw."Job runs" j
  GROUP BY "Date"
  ORDER BY "Date" DESC;

But with this query I get the following error:

SQL Error [42803]: ERROR: subquery uses ungrouped column "j.Start time" from outer query
  Position: 135

Anybody an idea how to solve this query issue, and get to the output I want to get?

After adapting the query as @a_horse_with_no_name suggested:

SELECT j."Start time"::date AS "Date",
  ["Job name", count(*)::varchar] as "Jobs"
  FROM amdw."Job runs" j
  GROUP BY "Date", "Job name"
  ORDER BY "Date" DESC;

    Date    |             Jobs             
-----------------------------------------------------------------------------
 21/11/2022 | {{TestJob1,1500}
 21/11/2022 | {TestJob2,1100}
 21/11/2022 | {TestJob3,500}}
 20/11/2022 | {{TestJob1,1300}
 20/11/2022 | {TestJob2,100}
 20/11/2022 | {TestJob3,500}}

So I now need to find a way to only show the date once and create a second dimension in the array...

CodePudding user response：

You can aggregate this in two steps, first you create a temporary table that count each Job name by Start time and then you aggregate your arrays with ARRAY_AGG()

-- Creation of a temporary table
WITH agg_by_date_and_job_name AS (
    SELECT
        "Start time" AS "Date",
        ARRAY ["Job name", count(*)::text] AS "Job"
    FROM
        amdw
    GROUP BY
        "Date",
        "Job name"
)
SELECT
    "Date",
    ARRAY_AGG("Job") AS "Jobs"
FROM
    agg_by_date_and_job_name
GROUP BY
    "Date"
ORDER BY
    "Date" DESC;