CODEWITHSUNDEEP
coldfusiongroup-bysumcoalesce
user745110
user745110

Reputation: 119

coalesce doesn't work when grouping is used

I have an easy question. I'm trying to use coalesce in combination with a group by clause. I want to get 0 values when the variable is null. Here is my sql server code:

SELECT SUM(COALESCE(NETTOTAL,0)) AS NETTOTAL,DATEPART(MM,RECORD_DATE) MONTH 
FROM ORDERS WHERE ORDER_EMPLOYEE_ID=#attributes.record_emp_id# 
GROUP BY DATEPART(MM,RECORD_DATE) ORDER BY MONTH

.. and my output:

<tr height="20">
    <td>Orders</td>
    <cfoutput query="get_orders"><td style="text-align:center;">#tlformat(nettotal,2)# - #month#</td></cfoutput>
</tr>

This code is just for the orders. There is also the sales row. Anyway here is the screenshot to make it more clear:

https://i.sstatic.net/VIAmr.png

To make it more clear I added the number of the month. As you can see the order is broken since there are no zero values for the other months...

P.S.Thank you all for the help! i really appreciate it!

Upvotes: 1

Views: 964

Answers (3)

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

Your query is not at fault. You are trying to select from ORDERS based on each employee_id. You are then looping over it.

If a given month has no orders, then there will be no row for it within the result set.

Even if there were only orders for the last 4 months, they would get pushed to the first 4 as you are not checking that the month you are currently outputting matches the column header.

For a bit of metacode, I would go down this route

1 - create an array as follows 

arrMonths= [
    {orders=0,sales=0},
    {orders=0,sales=0}....
]

This will give you a stc you can iterate over later.

2 - I would then loop over each query

<cfoutput query="get_orders">
    <cfset arrMonths[month].orders = nettotal>
</cfoutput>

3 - I would then iterate over the array

<tr height="20">
    <td>Orders</td>
    <cfoutput from="1" to="#ArrayLen(arrMonths)#" index="thisMonth">
        <td style="text-align:center;">#tlformat(arrMonths[thisMonth].orders,2)# - #thisMonth#</td>
    </cfoutput>
</tr>

This way, every month will ALWAYS have a value even if it's 0. You can also ditch the coalesce as the simple fact that rows with no orders have no records means they default to 0 so your query may become

SELECT 
    SUM(COALESCE(NETTOTAL)) AS NETTOTAL,
    DATEPART(MM,RECORD_DATE) MONTH 
FROM ORDERS 
WHERE ORDER_EMPLOYEE_ID=<cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.record_emp_id#">
GROUP BY DATEPART(MM,RECORD_DATE)

MONTH is now not necessary as it's just inserting into the array which deals with ordering

Upvotes: 3

Schiavini
Schiavini

Reputation: 2939

Try to invert the order of the functions Sum and Coalesce. IsNull might be more readable as well:

SELECT   IsNull(SUM(NETTOTAL), 0) AS NETTOTAL,
         DATEPART(MM, RECORD_DATE) MONTH 
FROM     ORDERS
WHERE    ORDER_EMPLOYEE_ID = #attributes.record_emp_id# 
GROUP BY DATEPART(MM, RECORD_DATE)
ORDER BY MONTH

If there are no elements of NETTOTAL, Coalesce from your code would not be called. So a Sum of no rows will be null.

Upvotes: 0

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

TRy ISNULL() Instead of COALESCE . COALESCE is used for multiple argumentsif multiple arguments are not needed you could use ISNULL

Upvotes: 0

Related Questions