Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
and the question is? –  ypercube Jan 24 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. –  Dexter Jan 24 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 at 10:27
    
I expect to have each name one time not 5 times. –  Dexter Jan 24 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 at 10:31

1 Answer 1

up vote 1 down vote accepted

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 ;
share|improve this answer
    
Works like charm , thanks a lot..could you tell me what was the wrong with my old code ? , i know this code looks simpler to read –  Dexter Jan 24 at 10:56
    
I tried the second method just to know it, added MAX() to my outer select but it still the same result.. –  Dexter Jan 24 at 12:45
    
Did you change the GROUP BY? –  ypercube Jan 24 at 13:06
    
I tried but them keep it as it was because postgre telling me that t.opened_complaints must be in a group by , and the same for the rest columns so i keep GROUP BY the same –  Dexter Jan 24 at 14:10
    
You should put MAX(t.opened_complaints), etc in the external select. –  ypercube Jan 24 at 14:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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