Cross join left join count row

Question

I have these tables:

Task Group table

id name 1 cooking 2 baking

Task Status table

id name 1 open 2 done? 3 completed 4 re-open 5 suspend

Tasks table

project_id task_group_id task_status_id task_name 1 1 1 cooking martabak 1 1 2 cooking sate kambing 1 1 3 cooking nasi uduk 1 1 4 cooking nasi goreng 1 1 1 cooking martabak telor 1 1 2 cooking sate ayam 1 1 5 cooking nasi tutug oncom 1 1 5 cooking nasi pera 1 2 1 bake nastar 1 2 2 bake bolu pisang 1 2 3 bake bika ambon 1 2 5 bake bolu kukus 1 2 3 bake cheese stik 1 2 1 bake bolu ketan 1 2 5 bake papais 1 2 1 bake boled

and the result that what i want is like this:

project task_group task_status number Restaurant Cooking Completed 1 Restaurant Cooking Done? 2 Restaurant Cooking Open 2 Restaurant Cooking Re-open 1 Restaurant Baking Completed 2 Restaurant Baking Done? 1 Restaurant Baking Open 3 Restaurant Baking Re-open 0

task status "suspend" is excluded

i have tried to cross join tasks table with task status table using subquery with where clause task_status.name in ('open', 'done?', 'completed', 're-open'), but i can't get the right count aggregation

just need to group by first table name and second table name — krishn Patel
i have tried it, but the count result is not right... i am sure i miss the link on join, but i don't know how i join it... — Rakaziwi

Arion Arion · Accepted Answer · 2017-06-06T08:14:30

I think you can do something like this:

SELECT
    project.name AS project,
    Task_Group.name as task_group ,
    Task_Status.name as task_status,
    COUNT(*) AS number
FROM
    Tasks
    JOIN Task_Group
        ON Tasks.task_group_id = Task_Group.id
    JOIN Task_Status
        ON Task.task_status_id=Task_Status.id
    JOIN project_table
        ON Task.project_id=project_table.project_id
WHERE 
    Task_Status.Id IN(1,2,3,4)
GROUP BY
    project_table.name,
    Task_Group.name,
    Task_Status.name

Cross join left join count row

1 Answers