Oracle group by and the empty resulting set

Question

Hi! I have the following SQL problem.

Scenario:

I have two tables: Change and ChangeTicket. There is a 1:n relationship. One Change has cero or many changeTickets. No change means no changeTickets. A changeTicket has a status (open/closed/...) A changeTicket has a field representing how many minutes took this change.

A Change has a company and a month. There is at most ONE change in the month.

I have to report for a given company and for a given month the sum of minutes a given change took.

I wrote the following SQL statement.

select  nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id

This is OK.

If for the given month and the given company there are neither changes nor tickets then I get a null value which is converted to cero usgin the NVL function.

The problem arises when I want to group the information using the status. If I add a grup clause

select  Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id 
group by Change_Ticket.status

then my result is the empty set. I understand that there is no status and then the resulting set is somehow consistent and then an empty resulting set is returned.

How can I avoid this problem. In this case I need to report an empty status and cero as the the sum.

(BTW, I've also tried putting nvl(Change_Ticket.status, 'none') but didn't work)

Thanks a lot in advance.

Luis

Answer 1

Assuming you have the list of your statuses in a table called statuses:

SELECT  statuses.id, nvl(sum(service_req), 0) as SUM_REQ 
FROM    statuses
LEFT JOIN
        Change
ON      Change.company_id = '0' 
        AND Change.month = '07'
        AND Change.status = statuses.id
LEFT JOIN
        Change_Ticket
ON      Change_Ticket.Change_Id  = Change.Id
GROUP BY
        statuses.id

or this (only if you are using 8i, this syntax is deprecated in higher versions):

SELECT  statuses.id, nvl(sum(service_req), 0) as SUM_REQ 
FROM    statuses, Change, Change_Ticket
WHERE   Change.company_id(+) = '0' 
        AND Change.month(+) = '07'
        AND Change.status(+) = statuses.id
        AND Change_Ticket.Change_Id(+)  = Change.Id
GROUP BY
        statuses.id

If you only want to show existing statuses or a NULL when no records exist, use this:

SELECT  statuses.id, nvl(sum(service_req), 0) as SUM_REQ 
FROM    dual
LEFT JOIN
        Change
ON      Change.company_id = '0' 
        AND Change.month = '07'
LEFT JOIN
        Change_Ticket
ON      Change_Ticket.Change_Id  = Change.Id
GROUP BY
        statuses.id

Answer 2

I think to achieve what you want you would need to do this:

select  Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id 
group by Change_Ticket.status
union all
select '' as STATUS, 0 as SUM_REQ
from dual
where not exists (select null from Change_ticket)