sql server - SQL Report Builder: Issue with SUM in VarChar Column

Question

I'm back with what is probably a very simple question but (as a SQL newbie) I can't seem to figure it out.

I'm basically running a fairly simple report for a hospital. They want a report that details each time a specific drug was prescribed from the Emergency Dept., along with a section on the report that lists the TOTAL number of units that were dispensed. This last part is what I'm having trouble with.

For the time being, I've added an extra column onto the Report Builder report that represents "Qty_Dispensed_Total" and I'd expect it to be the number of TOTAL units dispensed/prescribed, but instead it's showing a different number on each row and none of the numbers are a total.

One issue is that all fields are VarChar, but no matter what I try, I end up getting errors or incorrect "totals".

This is what I'm working with:

SELECT Dispensed_DTTM, 
MRN, 
Visit_Number, 
Medication_Description, 
Qty_Dispensed,
SUM(CAST(Qty_Dispensed AS INT)) AS Qty_Dispensed_Total,
Department_Name 

FROM dbo.T_SomeTable

WHERE (
CAST([Dispensed_DTTM] AS DATE) BETWEEN '01/01/2020' AND '12/31/2020' AND
Medication_Description LIKE '%Trazodone 50%' AND
Department_Name LIKE '%Emergency%'
)
GROUP BY Dispensed_DTTM, Qty_Dispensed, MRN, Visit_Number, Medication_Description, Department_Name
ORDER BY Dispensed_DTTM;

I added in the GROUP BY statement because I couldn't get the code to run without it.

I'm sure I'm missing something simple but I can't put my finger on it.

Just for clarification/TLDR: I want the Qty_Dispensed_Total field to be a single number that represents the grand total (sum) of all Qty_Dispensed rows.

Let me know if I can provide any additional information (or if you need a screenshot of the report).

question from:https://stackoverflow.com/questions/65910894/sql-report-builder-issue-with-sum-in-varchar-column

Answer 1

You can use a window function to compute the grand total, just add the OVER clause:

SUM(CAST(Qty_Dispensed AS INT)) OVER () AS Qty_Dispensed_Total,

You can then remove the GROUP BY clause, I would assume.

---

Another option, if you want the total as a new row instead of a column, is to use GROUP BY ROLLUP(... or GROUP BY GROUPING SETS(...:

GROUP BY GROUPING SETS (
    (Dispensed_DTTM, Qty_Dispensed, MRN, Visit_Number, Medication_Description, Department_Name),
    ( )    --empty set, so grand total
)

In this case, the grand-total row will have nulls in all the columns. If you want to display that a bit better, you can use SELECT ISNULL(column, 'Grand Total')