-1

I can't find a way to make Group by works in Postgresql, How can i make group by works so each name will be only one , not repeated many times like what i got in this picture?

Here is a code for what i have tried:

SELECT em_id , em_type, advisor_name, opened_complaints, in_progress_complaints, pending_complaints, closed_complaints, solved_complaints
FROM
  ((SELECT DISTINCT ON (advisor_id) advisor_id as em_id, employees.type as em_type ,employees.full_name as advisor_name, COUNT(ticket_statuses.status_id) AS opened_complaints, 0 AS in_progress_complaints, 0 AS pending_complaints, 0 AS closed_complaints, 0 AS solved_complaints
    FROM ticket_statuses, employees,statuses WHERE ticket_statuses.advisor_id = employees.id AND employees.type = 'Advisor'
                                          AND ticket_statuses.status_id = statuses.id AND statuses.ticket_status = 'Open'
    GROUP BY em_id, advisor_name, em_type)
   UNION
   (SELECT DISTINCT ON (advisor_id) advisor_id as em_id, employees.type as em_type ,employees.full_name as advisor_name, 0 AS opened_complaints, COUNT(ticket_statuses.status_id) AS in_progress_complaints, 0 AS pending_complaints, 0 AS closed_complaints, 0 AS solved_complaints
    FROM ticket_statuses, employees, statuses WHERE ticket_statuses.advisor_id = employees.id AND employees.type = 'Advisor'
                                          AND ticket_statuses.status_id = statuses.id AND statuses.ticket_status = 'In Progress'
    GROUP BY em_id, advisor_name, em_type)
   UNION
   (SELECT DISTINCT ON (advisor_id) advisor_id as em_id, employees.type as em_type,employees.full_name as advisor_name, 0 AS opened_complaints , 0 AS in_progress_complaints, COUNT(ticket_statuses.status_id)AS pending_complaints, 0 AS closed_complaints, 0 AS solved_complaints
    FROM ticket_statuses, employees, statuses WHERE ticket_statuses.advisor_id = employees.id AND employees.type = 'Advisor'
                                          AND ticket_statuses.status_id = statuses.id AND statuses.ticket_status = 'Pending'
    GROUP BY em_id, advisor_name, em_type)
   UNION
   (SELECT DISTINCT ON (advisor_id) advisor_id AS em_id, employees.type as em_type , employees.full_name as advisor_name, 0 AS opened_complaints , 0 AS in_progress_complaints, 0 AS pending_complaints, COUNT(ticket_statuses.status_id) AS closed_complaints, 0 AS solved_complaints
    FROM ticket_statuses, employees, statuses WHERE ticket_statuses.advisor_id = employees.id AND employees.type = 'Advisor'
                                          AND ticket_statuses.status_id = statuses.id AND statuses.ticket_status = 'Closed'
    GROUP BY em_id, advisor_name, em_type)
   UNION
   (SELECT DISTINCT ON (advisor_id) advisor_id AS em_id, employees.type as em_type ,employees.full_name as advisor_name, 0 AS opened_complaints , 0 AS in_progress_complaints, 0 AS pending_complaints, 0 AS closed_complaints, COUNT(ticket_statuses.status_id) AS solved_complaints
    FROM ticket_statuses, employees, statuses WHERE ticket_statuses.advisor_id = employees.id AND employees.type = 'Advisor'
                                          AND ticket_statuses.status_id = statuses.id AND statuses.ticket_status = 'Solved'
    GROUP BY em_id, advisor_name, em_type)
  ) t
GROUP BY t.em_id, t.advisor_name, t.opened_complaints, t.in_progress_complaints, t.pending_complaints, t.closed_complaints, t.solved_complaints, t.em_type ORDER BY t.em_id, t.advisor_name, t.opened_complaints, t.in_progress_complaints, t.pending_complaints, t.closed_complaints, t.solved_complaints, t.em_type;

The result was the following:

enter image description here

  • and the question is? – ypercubeᵀᴹ Jan 24 '14 at 10:23
  • how can i make group by works ? i searched alot for it but i cant figure out what is the wrong with my code. – Mostafa Hussein Jan 24 '14 at 10:25
  • Your code does not throw any error, so it is correct. Do you mean you want something different as a result and not what you get? – ypercubeᵀᴹ Jan 24 '14 at 10:27
  • I expect to have each name one time not 5 times. – Mostafa Hussein Jan 24 '14 at 10:29
  • I imagine you miss some aggregates there (no sum(), count() or similar can be seen in your outer query). Some example data and the expected results (together with a description how it is supposed to be calculated) are seriously missing from your question. – dezso Jan 24 '14 at 10:31
1

I suppose you want a PIVOT type of query. No need to overcomplicate it, something like this will work:

SELECT 
    em.id         AS em_id, 
    em.type       AS em_type,
    em.full_name  AS advisor_name, 
    COUNT(CASE st.ticket_status WHEN 'Open'        THEN 1 END) AS opened_complaints,
    COUNT(CASE st.ticket_status WHEN 'In Progress' THEN 1 END) AS in_progress_complaints,
    COUNT(CASE st.ticket_status WHEN 'Pending'     THEN 1 END) AS pending_complaints,
    COUNT(CASE st.ticket_status WHEN 'Closed'      THEN 1 END) AS closed_complaints,
    COUNT(CASE st.ticket_status WHEN 'Solved'      THEN 1 END) AS solved_complaints
FROM 
        ticket_statuses AS ti
   JOIN
        employees AS em
             ON  ti.advisor_id = em.id 
   JOIN
        statuses AS st
             ON  ti.status_id = st.id
WHERE 
    em.type = 'Advisor'
  AND
    st.ticket_status IN ('Open', 'In Progress', 'Pending', 'Closed', 'Solved')
GROUP BY 
    em.id,
    em.type,             -- these 2 lines are not strictly needed
    em.full_name ;       -- in Postgres recent versions

You could change your old code to get the same results, leaving the internal query untouched. This would aggregate each set of 5 rows per person you get from the internal query into one group. The MAX() would ensure that the 0s are discarded and the high values are kept:

SELECT 
    em_id, em_type, advisor_name, 
    MAX(t.opened_complaints) AS opened_complaints,
    --- ,                --- the rest of the columns with MAX() as above
FROM
  ---        --- unchanged
  ---        ...
  ---        --- unchanged
  ) t
GROUP BY 
    t.em_id, t.em_type, t.advisor_name ;

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.