sql - Unable to get the required output using a LIST, <cfloop> and <cfquery> -

i have list (query output)that has distinct id’s table “account”. using list looping on select query , extracting count . having trouble getting count value on individual basis.

the table “account” has fields 1) contract_id ,
2)vaccountid(primary key) , 3) status_id (values=’’,v,c).

i doing following query

    <cfquery name="qrygetid" datasource="#datasource#">  select distinct(contract_id )    account     order contract_id desc </cfquery>   <!---   account details each ---> <cfset  z =#valuelist(qrygetid.id)# > <cfloop list="#z#" index="y"   >   <cfquery name="qrygetniceaccounts"  datasource="#datasource#">  select   distinct(a.contract_id )  ,(select count(vaccountid) account         c _id in (<cfqueryparam value="#x#" list="yes" cfsqltype="cf_sql_integer" separator=",">)         , status_id = 'v' )   valid_acntv  ,(select count(vaccountid) account         c _id in (<cfqueryparam value="#x#" list="yes" cfsqltype="cf_sql_integer" separator=",">)         , status_id = 'c' )   valid_acntc      account       a.contract_id  in (<cfqueryparam value="#x#" list="yes" cfsqltype="cf_sql_integer" separator="," >)  order   contract_id   desc  </cfquery> 

the query ="qrygetniceaccounts" returning 1 value “valid_acntcount” different “c_id” in list .

example if “account” table has values

contract_id      count(vid)/ v_accoun t=’v’     count(vid)/ v_accoun t=’c’      123           10                                                  220     124           05                                                  110     123           01                                                     0   contract_id   count(vid)/ v_accoun t=’v’     count(vid)/ v_accoun t=’c’     123           10                                                  220     124           10                                                  220     123           10                                                  220 

basically having trouble getting counts individual ids.

side note:-when dump input “contract_id ” showing 123, 123 123 rather 123,124,125

[…] using list looping on select query , extracting count. […]

this bad idea. "getting counts" 1 of easiest things directly in sql , more not there no reason execute select query in loop. avoid whenever can performance reasons.

and in case it's avoidable (it makes code lot simpler), change sql:

<cfquery name="qrygetniceaccounts"  datasource="#datasource#">   select     contract_id,     (select count(vaccountid) v_account        c_id = a.contract_id , status_id = 'v'     ) valid_acntv,     (select count(vaccountid) v_account        c_id = a.contract_id , status_id = 'c'     ) valid_acntc       (select contract_id account group contract_id)   order     contract_id desc </cfquery> 

you don't need other query @ all, neither need loop.

an alternative way express same this:

select   a.contract_id,   sum(case status_id when 'v' 1 else 0 end) valid_acntv,   sum(case status_id when 'c' 1 else 0 end) valid_acntc   account   inner join v_account c on c.c_id = a.contract_id group    a.contract_id order   a.contract_id desc 

this hit v_account view once. must determine efficient query.