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'