Oracle SQL Count function-CodePudding

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

CodePudding user response：

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'

CodePudding user response：

The use of COUNT (as other aggregate functions) is simple.

If you want to add an aggregate function, please group all scalar fields in the GROUP BY clause.

So, in the SELECT you can manage field1, field2, count(1) and so on but you must add in group by (after where conditions) field1, field2

Try this:

select
h.employee as "Name",
count(1) as "total"
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'
GROUP BY h.employee