Count number of occurencies

Question

I have got a query:

    SELECT DISTINCT departments.department AS dep, array_agg(deps_with_computers.list_of_computers) AS arr
  FROM departments
    LEFT JOIN
    (SELECT departments.department, stud_in_class.list_ids AS list_of_computers
    FROM departments LEFT JOIN
    (SELECT departments.course, array_to_string(array_agg(ids_with_computers.computers),', ') AS list_ids
    FROM departments
    LEFT JOIN
      (SELECT students.student_id AS id, array_to_string(array_agg(m.medium),', ') AS computers, students.course
       FROM students LEFT JOIN
      (SELECT computers.medium, have_got_computers.student_id FROM have_got_computers LEFT JOIN computers ON have_got_computers.medium_id = computers.medium_id) AS m
      ON students.student_id = m.student_id
        GROUP BY students.student_id) AS ids_with_computers

        ON departments.course = ids_with_computers.course GROUP BY departments.course) AS stud_in_class
     ON departments.course = stud_in_class.course GROUP BY departments.department, stud_in_class.list_ids)
  AS deps_with_computers
ON departments.department = deps_with_computers.department
GROUP BY departments.department);

And it returns me

dep     |  arr
----------------
somthing| {element}
nextdep | {e1, e2, e4, e7}
nextdep2| {e1, e1, e4, e7, e1, e4}

And I would like to have result like:

dep     | occurencies               |sum
----------------------------------------
somthing| element: 1                |1
nextdep | e1:1, e2: 1, e4: 1, e7: 1 |4
nextdep2| e1:3  e4: 2, e7: 1        |6

Is there any simple way to do it in postgresql?

Answer 1

If I understand correctly, you can manage to get a table (or view) with rows for department and element, and you'd like the number of rows grouped by department and element, and then the result aggregated with the sum per department.

You can achieve the first part by counting the rows grouped by department, element. To achieve the second part as shown in your example, you can build a JSON value (json_object_agg is only available from PostgreSQL 9.4 onwards).

WITH items (id, dep, element) AS (
    VALUES (1, 'dep1', 'E1'),
           (2, 'dep1', 'E2'),
           (3, 'dep1', 'E2'),
           (4, 'dep1', 'E2'),
           (5, 'dep1', 'E3'),
           (6, 'dep1', 'E3'),

           (7, 'dep2', 'E1'),
           (8, 'dep2', 'E1'),
           (9, 'dep2', 'E3'),

           (10, 'dep3', 'E1'),
           (11, 'dep3', 'E2'),
           (12, 'dep3', 'E4'),
           (13, 'dep3', 'E4'),
           (14, 'dep3', 'E4')
),
element_counts_per_department AS (
   SELECT dep, element, COUNT(id) AS element_count
   FROM items
   GROUP BY dep, element ORDER BY dep, element
)
SELECT dep,
    json_object_agg(element, element_count),
    SUM(element_count)
FROM element_counts_per_department
GROUP BY dep ORDER BY dep

The intermediate query (element_counts_per_department) will look like this:

|  dep | element | element_count |
|------|---------|---------------|
| dep1 |      E1 |             1 |
| dep1 |      E2 |             3 |
| dep1 |      E3 |             2 |
| dep2 |      E1 |             2 |
| dep2 |      E3 |             1 |
| dep3 |      E1 |             1 |
| dep3 |      E2 |             1 |

The final aggregation will look like this:

|  dep |                                  |     |
|------|----------------------------------|-----|
| dep1 | { "E1" : 1, "E2" : 3, "E3" : 2 } |   6 |
| dep2 | { "E1" : 2, "E3" : 1 }           |   3 |
| dep3 | { "E1" : 1, "E2" : 1, "E4" : 3 } |   5 |