Click here to Skip to main content
15,358,528 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Please look into below sql code

What I have tried:

SQL
from CUSTOMER as c
inner join AGENTS as a
on c.AGENT_CODE = a.AGENT_CODE
inner join ORDERS as o
on c.AGENT_CODE = o.AGENT_CODE
where c.WORKING_AREA = 'London'
group by c.CUST_NAME, c.WORKING_AREA, a.AGENT_NAME, a.WORKING_AREA, o.ORD_DATE, o.ADVANCE_AMOUNT, COUNT(AGENT_NAME)
having COUNT(AGENT_NAME)>1;
Posted
Updated 12-Mar-22 17:45pm
v2

1 solution

You cannot use aggregate function like "count" in group by cluse.
Just remove count(AGENT_NAME) from group by cluse.


select c.CUST_NAME, c.WORKING_AREA as CUST_WA, a.AGENT_NAME, a.working_area as AGENT_WA, o.ORD_DATE, o.ADVANCE_AMOUNT,
count(AGENT_NAME) as TotalCount
from CUSTOMER as c
inner join AGENTS as a
on c.AGENT_CODE = a.AGENT_CODE
inner join ORDERS as o
on c.AGENT_CODE = o.AGENT_CODE
where c.WORKING_AREA = 'London'
group by c.CUST_NAME, c.WORKING_AREA, a.AGENT_NAME, a.WORKING_AREA, o.ORD_DATE, o.ADVANCE_AMOUNT
having COUNT(AGENT_NAME)>1;
   
Comments
Maciej Los 13-Mar-22 10:40am
   
This is not an answer, but some additional information about your issue. So, remove this and use Improve question[^] widget to update your question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900