Skip to content
Advertisement

Oracle SQL Count function

I am hoping someone can advise on the below please? I have some code (below), it is pulling the data I need with no issues. I have been trying (in vain) to add a COUNT function in here somewhere. The output I am looking for would be a count of how many orders are assigned to each agent. I tried a few diffent things based on other questions but can’t seem to get it correct. I think I am placing the COUNT ‘Agent’ statement and the GROUP BY in the wrong place. Please can someone advise? (I am using Oracle SQL Developer).

select
n.ordernum as "Order",
h.employee as "Name"
from ordermgmt n, orderheader h
where h.ordernum = n.ordernum
and h.employee_group IN ('ORDER.MGMT')
and h.employee is NOT NULL
and n.percentcomplete = '0'
and h.order_status !='CLOSED'

Output I am looking for would be, for example:

Name         Orders Assigned
Bob                 3
Peter               6
John                2

Thank you in advance

Name Total
49
49
49
49
49
John 4
John 4
John 4
John 4
Peter 2
Peter 2
Bob 3
Bob 3
Bob 3

for example. so there are 49 blank rows summed up as 49 in the Total column. I did not add the full 49 blank columns to save space

Advertisement

Answer

Would be easier with sample data and expected output, but maybe you are looking for something like this

select
  n.ordernum as "Order",
  h.employee as "Name",
  count(*) over (partition by h.employee) as OrdersAssigned
from ordermgmt n, orderheader h
where h.ordernum = n.ordernum
  and h.employee_group IN ('ORDER.MGMT')
  and h.employee is NOT NULL
  and n.percentcomplete = '0'
  and h.order_status !='CLOSED'
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement