Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                Edit your question with a sample of data. Also I don't see any group by or count in your code.
– sagi
                Jan 25, 2022 at 13:37
                That looks like a simple aggregation. How are ordermgmt and orderheader related? One row per ordernum in orderheader and many rows per ordernum in orderheader? What do the tables represent? orderheader is an order?, and ordermgmt is what?
– Thorsten Kettner
                Jan 25, 2022 at 13:44
                Sample data makes more sense, when you don't only show the result, but also the table data that leads to this result. What would orderheader and ordermgmt contain, so you get the result you are showing?
– Thorsten Kettner
                Jan 25, 2022 at 13:59
                You are using an antique join syntax by the way that makes your query look like it was written in the 1980s. Please use exlicit joins instead (INNER JOIN, LEFT OUTER JOIN, etc.).
– Thorsten Kettner
                Jan 25, 2022 at 14:01
                Thank you Thorsten, I will update the join type, I am rather new here so still learning. orderheader is the order details and ordermgmt is the admin table in the backend. The relationship is ordernum to ordernum. I am trying to add the output but not sure how to do so
– John Williams
                Jan 25, 2022 at 14:09
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'
                Thank you James, this one works better however I now have multiple blank lines in the employee field. The count is working however it repeats. For example John has 4 orders assigned.  so I have  John  4  four times in a row
– John Williams
                Jan 25, 2022 at 13:49
                "I now have multiple blank lines in the employee field" => They should be present before this query, I just added the OrderAssigned field.  "I have John 4 four times in a row" => If you want to keep the Order field, you cannot get a single view of John. Remove this field, and use the query provided by the other answer.
– James
                Jan 25, 2022 at 14:10
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.