Passing Arraylist to SQL query to get the desired result

Question

I wrote a query like this:

select agent_id,count(id) as agentCount from demo.table lst where lst.agent_id in(:idpList) group by agent_id order by agentCount ASC;

but this only returns the agent_id which are in idp List i also want to get the ids that are in the list but not in demo.table and return count as 0 . i am not able to figure out how can i do that.

eg : List : [20,17,16,15,50] so 50 is not the table but yet i want 50 to be included in the end result.

i have two DB from one i am getting all the agent_id and storing it in List and in another DB i have table that stores the id and no of task associated with these id so i want to check how many times task has been created corresponding to these agent_id and get the count but as these some of agent_id are present in 2nd DB table but some are not so for the one that are not present count should return 0 but from this query i am getting the count of only those that are present in the second DB table

Help would be appreciated.

for now the output of this query is agent_id agentcount 20 2 17 3 16 1 15 1 but i want 50 also with the count 0 — Garima Joshi, May 13 '20 at 04:51
hello , your requirement is not clear , can you please try to restate the requirement — Ashish Shetkar, May 13 '20 at 04:55

score 0 · Answer 1 · answered May 13 '20 at 05:43

0

try this:

select count(*),A.agent_id from 
(
select 20 as agent_id
union select 17
union select 16
union select 15
union select 50 ) as A  LEFT JOIN demo.table s on s.agent_id = A.agent_id where s.agent_id in(:idpList)  group by s.agent_id;

But you have to create this part ( select 20 as agent_id union select 17 union select 16 union select 15 union select 50 ) of query manually.

Otherwise there is no way to this.

answered May 13 '20 at 05:43

Sachin Kumar

1,055
9
15

but the list is dynamic and the size too – Garima Joshi May 13 '20 at 06:04
yes I know, it is dynamic, that why i have written in my answer to handle this manually,as per user dynamic just create the first part programmatically then take join as per my query. – Sachin Kumar May 13 '20 at 06:11

Passing Arraylist to SQL query to get the desired result

1 Answers1