sql - SQL Report Builder: Issue with SUM in VarChar Column
问题描述
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).
解决方案
The Qty_Dispensed_Total
that you are getting, is the Total of the group.
In your case, the group is Dispensed_DTTM, Qty_Dispensed, MRN, Visit_Number, Medication_Description, Department_Name
Your best shot is to make a subquery to get the Total, instead of grouping.
If you post the query to create the table T_SomeTable
, I could write the query for you.
推荐阅读
- python - Raspberry Pi 上的文本文件损坏
- python - 如何加载列表,向其中添加元素然后再次存储?
- python - Pandas 合并同名列
- javascript - 当窗口变量在反应中发生变化时如何更新状态
- spring-boot - @BeforeStep 未在 AsyncProcessor 中调用
- c++ - 为什么这个对象不必调用它的构造函数?
- ssl-certificate - Discord.py 证书验证失败:
- mysql - 当我在 Mysql 中尝试在我的表中进行部分搜索时,它给了我 Error Empty set (0.00 sec)
- python - 使用 pandas 从 datetime 对象转换从午夜开始的毫秒数
- c# - Unity 2D Trigger 只能由一个 Player Prefab 触发