I have the following lines of code
Select 0 as Stipend
and then to actually add a value to stipend I do this
COALESCE(SUM(sdhi.stipend), 0, CONVERT(decimal(16, 4), 0)) Stipend
But it returns as 0 and not as a decimal for the columns, how can I convert this to decimal?
0.000 is the same as 0. The DECIMAL data type does not define the formatting of the value, just how it is stored. This means, the representation of said value depends on the client software, like SSMS or a web app.
Formatting the value should be done by the client software (SSMS does it for you with it's current default settings, others might not)
This line is overcomplicated:
COALESCE(SUM(sdhi.stipend), 0, CONVERT(decimal(16, 4), 0)) Stipend
Besides that, the data type of literal value 0 is decimal(1,0), therefore the result will be decimal(1,0) when SUM(sdhi.stipend) is NULL (which can only happen if you have only NULL values in the group you are summing or the table is empty).
This is enough if you want to force DECIMAL(16,4) as the data type.
COALESCE(SUM(sdhi.stipend), CONVERT(decimal(16, 4), 0)) Stipend
Be aware, that the result of SUM(sdhi.stipend) might have different precision and scale (depending on the data you have stored in the column). Therefore you might want to consider the following:
CONVERT(decimal(16, 4), COALESCE(SUM(sdhi.stipend), 0)) AS Stipend
The problem with this is that when the result of SUM(sdhi.stipend) exceeds the limits of decimal(16, 4) you'll get overflow errors.
Choose wisely and read a bit about decimal arithmetic in SQL Server.
SQL Server COALESCE() function returns the first non-null value in a list:
Your column sdhi.stipend data is NULL then your second argument is 0 then result is second argument.
If column sdhi.stipend data type is decimal or money you need this:
Select COALESCE(SUM(sdhi.stipend), CONVERT(decimal(16,4), 0 )) as Stipend