I have the following function that selects the total donations and then reorders them by month. I'm using this data to feed a bar chart that will list the months 1–12, however if there are no donations made in a given month, the function returns nothing which means there are less than 12 months showing on my bar chart. Not ideal.

ExecuteSQL( "SELECT SUM(Total), MonthReorder FROM Donations WHERE DateCreation >= StartDate AND DateCreation <= EndDate GROUP BY MonthReordeR ORDER BY 2 ASC "; ¶; ¶)

returns

2724.22

a

574.61

b

229.61

c

695

d

2000

e

1101

f

500

h

870

i

150

j

17

k

20

l

where every letter represents a month. As you can see there were no donations made during month "g" so it returns nothing, but I would actually like the result to show a value of 0 instead of null as shown below:

2724.22

a

574.61

b

229.61

c

695

d

2000

e

1101

f

**0**

**g**

500

h

870

i

150

j

17

k

20

l

Another way, not tested

ExecuteSQL("SELECT

SUM(CASE WHEN Month(DateCreation)=1 THEN Total ELSE 0 END),

SUM(CASE WHEN Month(DateCreation)=2 THEN Total ELSE 0 END),

...

SUM(CASE WHEN Month(DateCreation)=12 THEN Total ELSE 0 END)

FROM Donations WHERE DateCreation >= StartDate AND DateCreation <= EndDate

";¶;"")

Making month field or repeated 12 calculation CASE..END field may be good for performance.

In this way, you can get any number of years using GROUP BY year(DateCreation) with one SQL.