0

I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of each group is available for each task.

Here are my tables:

select * from profiles;

 id |  name   | group
----+---------+---------
  1 |  name_1 | 1
  2 |  name_2 | 2
  3 |  name_3 | 3
  4 |  name_4 | 3
  5 |  name_5 | 3


select * from tasks;

 id | name
----+--------
  1 | task_1
  2 | task_2
  3 | task_3


select * from profiles_used;

 id | task.id | profile.id
----+---------+------------
  0 | 1       | 1
  1 | 1       | 2
  2 | 2       | 1
  3 | 3       | 4
  3 | 3       | 5

Magic: Run query that finds out how many not used profiles in each group available for each task.

Result:

             profiles.group
 task.id |  1  |  2  |  3  
---------+-----+-----+-----
       1 |  0  |  0  |  3
       2 |  0  |  1  |  3
       3 |  1  |  1  |  1

Please help me with right direction to start.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.