TryCF Gist

trycf-gist-1691049519301-608ffbf7-62e7-39d3-c046-19561632346d.cfm

<cfset qExpEmp = QueryNew("payrollyear, amounta, xhour, xday, aprice, clientnr",
"Integer,Decimal,Decimal,Integer,Decimal,varchar")>
<cfscript>
    queryAddRow(qExpEmp);
    querySetCell(qExpEmp,"payrollyear","2022");
    querySetCell(qExpEmp,"amounta","-216.81");
    querySetCell(qExpEmp,"xhour","-8.25");
    querySetCell(qExpEmp,"xday","1");
    querySetCell(qExpEmp,"aprice","26.2800");
    querySetCell(qExpEmp,"clientnr",nullValue());
    
    queryAddRow(qExpEmp);
    querySetCell(qExpEmp,"payrollyear","2022");
    querySetCell(qExpEmp,"amounta","-216.81");
    querySetCell(qExpEmp,"xhour","-8.25");
    querySetCell(qExpEmp,"xday","1");
    querySetCell(qExpEmp,"aprice","26.2800");
    querySetCell(qExpEmp,"clientnr",6);
</cfscript>
<cftry>
    <Cfquery name="q" dbtype="query">
        SELECT  payrollyear,
                sum(amounta) as amounts,
                sum(xhour) as vhour,
                sum(xday) as vday,
                sum(aprice) as totprice,
                IFNULL(clientnr,''), count(*) as rows
        FROM    qExpEmp
        WHERE   payrollyear = '2022'
                AND IFNULL(clientnr,5) = '5'
        group by payrollyear, price, clientnr
    </Cfquery>
    <cfdump var=#q#>
    <cfcatch><cfoutput>#cfcatch#</cfoutput></cfcatch>
</cftry>

<cftry>
    <Cfquery name="q" dbtype="query">
        SELECT  payrollyear
        FROM    qExpEmp
        WHERE   payrollyear = '2022'
    </Cfquery>
    <cfdump var=#q#>
    <cfcatch><cfoutput>#cfcatch#</cfoutput></cfcatch>
</cftry>