SQL SUM遇到问题 [英] SQL SUM HAVING Problem

问题描述

我正在尝试编写一个查询，生成一个用户列表，其电话费用总计超过15.00，但我是有困难。这是我的查询;

Hi,

I''m trying to write a query, which produces a list of users, whose telephone call costs total over 15.00 for the month, but I''m having difficulty. Here''s my query;

展开 | 选择 | Wrap | 行号

推荐答案

具体问题是什么？ ？


我想你想要变换> getdate（） - 30在where子句中，而不是having子句。您希望在执行任何计算之前按日期限制记录。此外，您包含userid但未在group by子句或聚合函数中使用它的事实也可能导致问题。


最后，您有'引号中的'15 .0000''，但sum函数将返回一个数字。将数字与字符串进行比较会导致数据类型不匹配错误。


试试这个，如果您仍然遇到问题，请告诉我们。

What problem are you having specifically?

I think you want transdate > getdate() - 30 in a where clause, not a having clause. You want to restrict the records by date before performing any calculations. Also, the fact that you''re including userid but not using it in a group by clause or in an aggregate function might be causing a problem as well.

Finally, you have ''15.0000'' in quotes, but the sum function is going to return a number. Comparing a number to a string like that results in a data type mismatch error for me.

Try this, and let us know if you''re still running into problems.

展开 | 选择 | Wrap | 行号

很好的答案Gershwyn :)你看到了我所看到的所有问题的底部，还有一些我错过了。


这应该很好地解决你所有的问题Pendragon。


欢迎来到 Bytes ！

Nice answer Gershwyn :) You got to the bottom of all the issues I saw, and some I missed.

That should solve pretty well all your problems Pendragon.

Welcome to Bytes!

感谢您的帮助。这是一种享受。只需要对如何自动创建该报告进行一些研究，并每周通过电子邮件发送给用户。我正在考虑创建文本.sql osql文件，计划任务和使用我们自己的SMTP实用程序。但是，创建一个SQL作业可能更容易，我认为SQL有一个内置的电子邮件功能，可以快速搜索帮助。暂时没有权限检查。


再次感谢。非常感谢。

Thanks for your help guys. That worked a treat. Just need to do some research on how to automate the creation of that report and email it to a user every week. I was thinking of something along the lines of creating a text .sql osql file, a scheduled task and using our own SMTP utility. However it might be easier creating a SQL job and I think SQL has its own in-built email function from a quick search of Help. Just don''t have the permissions to check right now.

Thanks again. Much appreciated.

这篇关于SQL SUM遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！