Hi,
I have the following tables: tms Taks, tms Sprint and tms TaskProjectRelationship and I want a query with this result in a bar chart where you can filter after the project to see which project has how many tasks comited or completed. It should look like this:
The Spints should be in the x-axix and the number of comited and completed tasks for each sprint should be in the y-axix
Tms Tasks has a column "Sprint" with a foreign key from the tms Sprint table. tms TaskProjectRelationship is a table where you have a column for the task id and a column for the project id next to it. With the editor I created a sql-query which looks like this:
SELECT "Tms Sprint - Sprint"."name" AS "Tms Sprint - Sprint__name", ("public"."tms_Task"."status"#>> '{detail}'::text[])::text AS "status → detail", count(*) AS "count"
FROM "public"."tms_Task" INNER JOIN "public"."tms_Sprint" "Tms Sprint - Sprint" ON "public"."tms_Task"."sprint" = "Tms Sprint - Sprint"."id" INNER JOIN "public"."tms_TaskProjectRelationship" "Tms TaskProjectRelationship" ON "public"."tms_Task"."id" = "Tms TaskProjectRelationship"."task"
GROUP BY "Tms Sprint - Sprint__name", "status → detail"
ORDER BY "Tms Sprint - Sprint"."name" ASC, "status → detail" ASC
And I get this result:
But I want to have the status → detail grouped by multiple values like in the first picture where every status -> detail is "comited" and where the tasks with the status -> detail "closed" is "completed". I have seen this post an stackoverflow sql - Group by multiple values in one column - Stack Overflow but if I try it out I get problems with the synthax. How can I implement this and is it right to use a inner join in my query?