sql - 每月报告的 TSQL Sum by group-6ren

sql - 每月报告的 TSQL Sum by group

转载作者：行者123 更新时间：2023-12-04 04:58:25

我正在尝试针对我们数据库中的一组运输条目生成一份报告(每月、年初至今等)，以显示正确的总金额并按运输公司对它们进行分组。我怎样才能完成获取所有这些信息并生成正确的报告？

需要注意的一件事是，由于使用来自各种来源的奇数字符导入文本，该表中的所有条目都是 nvarchar(max)。

主数据库列表如下所示:

表-shipping_info

shipping_company | ship_date | shipping_category | shipping_cost

UPS              | 20130301  |  CD'S             |  3.50
UPS              | 20130310  |  Records          |  4.50
UPS              | 20130322  |  CD'S             |  7.50
UPS              | 20130313  |  Tapes            |  12.44
UPS              | 20130324  |  CD'S             |  3.50
UPS              | 20120312  |  Records          |  4.50
UPS              | 20120304  |  CD'S             |  5.50
UPS              | 20120306  |  Tapes            |  3.50
UPS              | 20130309  |  CD'S             |  3.50
USPS             | 20130301  |  Tapes            |  3.50
USPS             | 20130301  |  CD'S             |  5.50
USPS             | 20130301  |  CD'S             |  4.50
USPS             | 20130201  |  Tapes            |  3.50
USPS             | 20130201  |  CD'S             |  3.50
USPS             | 20130201  |  Records          |  7.50
USPS             | 20130201  |  Tapes            |  9.50
USPS             | 20130201  |  CD'S             |  12.50
USPS             | 20120301  |  CD'S             |  14.50
USPS             | 20120301  |  Records          |  3.50
USPS             | 20130301  |  CD'S             |  23.50
USPS             | 20120301  |  Tapes            |  15.50
USPS             | 20120301  |  CD'S             |  34.50
DHL              | 20120301  |  Tapes            |  35.50
DHL              | 20120301  |  CD'S             |  3.50
DHL              | 20130301  |  Tapes            |  3.50
DHL              | 20130401  |  CD'S             |  3.50
DHL              | 20130401  |  Records          |  4.50
DHL              | 20130501  |  CD'S             |  4.50
DHL              | 20120201  |  Tapes            |  5.50
DHL              | 20120101  |  CD'S             |  6.50
DHL              | 20120501  |  CD'S             |  3.50
DHL              | 20120301  |  Tapes            |  7.50

我的目标是获得一个最终列表，该列表将按 Shipping_company 打印每月总运费的过滤列表。接下来，我想得到一份迄今为止的 list 和类似的东西。无论如何，我希望月度报告发生的事情是这样的:

UPS Shipping Cost Report for March 2013

CD's - $23.50
Records - $9.00
Tapes - $15.94

我首先尝试的是

SELECT shipping_company, ship_date, shipping_category, shipping_cost FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '')  GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost

这产生了一个很好的过滤表，但我仍然需要计算出总和并按类别将总数分组在一起。

然后我尝试了这个......

SELECT shipping_company, ship_date, shipping_category, SUM(shipping_cost) FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '')  GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost

由于这是一个 NVARCHAR(MAX) 表，t-sql 出错了，错误告诉我我需要将 shipping_cost 转换为 int。

所以......我接下来尝试了这个。

SELECT shipping_company, ship_date, shipping_category, convert(decimal(10, 2), shipping_cost) FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '')  GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost

哪种有效，哪种无效。

现在，当涉及到将这个列表分解为可报告的部分时，我很难从第一个示例中的哪个方向继续进行。我是否走在正确的道路上，将我的信息转化为我需要的月度报告格式？

::编辑/更新::

这很棒，而且很有效。我花了一些时间来完成这段代码的每一步，现在我明白了这里发生的事情背后的理论。这里还有最后一件事我很纠结。由于该表是 NVARCHAR(MAX)，我在尝试将 ship_date 转换为数字时遇到了挑战，但没有成功。

我采用了下面的原始代码 - 示例 1:

select  
CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL'
            ELSE shipping_company END AS shipping_company,
CASE WHEN (GROUPING(SUBSTRING(ship_date, 1, 6)) = 1) THEN 'TOTAL'
            ELSE SUBSTRING(ship_date, 1, 6) END AS Date,
CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL'
            ELSE shipping_category END AS shipping_category,
sum(convert(decimal(10,2),shipping_cost))
from tbl
group by shipping_company, SUBSTRING(ship_date, 1, 6), 
shipping_category WITH rollup

然后添加数字转换命令，如下所示 - 示例 2:

select  
CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL'
            ELSE shipping_company END AS shipping_company,
CASE WHEN (GROUPING(CONVERT(numeric, (SUBSTRING(ship_date, 1, 6))) = 1) THEN 'TOTAL'
            ELSE (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))) END AS Date,
CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL'
            ELSE shipping_category END AS shipping_category,
sum(convert(decimal(10,2),shipping_cost))
from tbl
group by shipping_company, (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))), 
shipping_category WITH rollup

但仍然将 nvarchar 转换为数字错误。然后我像这样移动了东西 - 示例 3:

select  
CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL'
            ELSE shipping_company END AS shipping_company,
CASE WHEN (CONVERT(numeric, GROUPING(SUBSTRING(ship_date, 1, 6))) = 1) THEN 'TOTAL'
            ELSE (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))) END AS Date,
CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL'
            ELSE shipping_category END AS shipping_category,
sum(convert(decimal(10,2),shipping_cost))
from tbl
group by shipping_company, (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))), 
shipping_category WITH rollup

并得到“分组函数的参数 1 与 GROUP BY 子句中的任何表达式都不匹配”，这是有道理的。我真的很想了解背后的理论，不仅是为什么第二个例子不起作用，而且是使转换起作用的正确行动方案是什么。

最佳答案

试试这个查询

我从日期中提取了年份和月份的部分，因此分组发生在特定年份的每个月。

select 
   shipping_company, 
   SUBSTRING(ship_date, 1, 6), 
   shipping_category, 
   sum(convert(decimal(10,2),shipping_cost))
from 
   tbl
group by 
   shipping_company, 
   SUBSTRING(ship_date, 1, 6), 
   shipping_category

SQL FIDDLE :

| SHIPPING_COMPANY | COLUMN_1 | SHIPPING_CATEGORY | COLUMN_3 |
--------------------------------------------------------------
|              DHL |   201201 |              CD'S |      6.5 |
|              DHL |   201202 |              CD'S |      5.5 |
|              DHL |   201203 |              CD'S |     46.5 |
|              DHL |   201205 |              CD'S |      3.5 |
|              DHL |   201303 |              CD'S |      3.5 |
|              DHL |   201304 |              CD'S |        8 |
|              DHL |   201305 |              CD'S |      4.5 |
|              UPS |   201203 |              CD'S |      5.5 |
|              UPS |   201203 |           Records |      4.5 |
|              UPS |   201203 |             Tapes |      3.5 |
|              UPS |   201303 |              CD'S |       18 |
|              UPS |   201303 |           Records |      4.5 |
|              UPS |   201303 |             Tapes |    12.44 |
|             USPS |   201203 |              CD'S |       68 |
|             USPS |   201302 |              CD'S |     36.5 |
|             USPS |   201303 |              CD'S |       37 |

如果您想要年度总数，那么您可以使用 WITH ROLLUP .

注意将年度总计视为每月总计...

查询 1 :

select  
CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL'
            ELSE shipping_company END AS shipping_company,
CASE WHEN (GROUPING(SUBSTRING(ship_date, 1, 6)) = 1) THEN 'TOTAL'
            ELSE SUBSTRING(ship_date, 1, 6) END AS Date,
CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL'
            ELSE shipping_category END AS shipping_category,
sum(convert(decimal(10,2),shipping_cost))
from tbl
group by shipping_company, SUBSTRING(ship_date, 1, 6), 
shipping_category WITH rollup

SQL FIDDLE :

| SHIPPING_COMPANY |   DATE | SHIPPING_CATEGORY | COLUMN_3 |
------------------------------------------------------------
|              DHL | 201201 |              CD'S |      6.5 |
|              DHL | 201201 |      Yearly TOTAL |      6.5 |
|              DHL | 201202 |              CD'S |      5.5 |
|              DHL | 201202 |      Yearly TOTAL |      5.5 |
|              DHL | 201203 |              CD'S |     46.5 |
|              DHL | 201203 |      Yearly TOTAL |     46.5 |
|              DHL | 201205 |              CD'S |      3.5 |
|              DHL | 201205 |      Yearly TOTAL |      3.5 |
|              DHL | 201303 |              CD'S |      3.5 |
|              DHL | 201303 |      Yearly TOTAL |      3.5 |
|              DHL | 201304 |              CD'S |        8 |
|              DHL | 201304 |      Yearly TOTAL |        8 |
|              DHL | 201305 |              CD'S |      4.5 |
|              DHL | 201305 |      Yearly TOTAL |      4.5 |
|              DHL |  TOTAL |      Yearly TOTAL |       78 |
|              UPS | 201203 |              CD'S |      5.5 |
|              UPS | 201203 |           Records |      4.5 |
|              UPS | 201203 |             Tapes |      3.5 |
|              UPS | 201203 |      Yearly TOTAL |     13.5 |
|              UPS | 201303 |              CD'S |       18 |
|              UPS | 201303 |           Records |      4.5 |
|              UPS | 201303 |             Tapes |    12.44 |
|              UPS | 201303 |      Yearly TOTAL |    34.94 |
|              UPS |  TOTAL |      Yearly TOTAL |    48.44 |
|             USPS | 201203 |              CD'S |       68 |
|             USPS | 201203 |      Yearly TOTAL |       68 |
|             USPS | 201302 |              CD'S |     36.5 |
|             USPS | 201302 |      Yearly TOTAL |     36.5 |
|             USPS | 201303 |              CD'S |       37 |
|             USPS | 201303 |      Yearly TOTAL |       37 |
|             USPS |  TOTAL |      Yearly TOTAL |    141.5 |
|            TOTAL |  TOTAL |      Yearly TOTAL |   267.94 |

编辑

好的，您在更新的查询中遇到了问题。 If 语句的两种情况都应返回相同的结果数据类型，因此当您尝试将日期值转换回数字时，您的真实情况将返回 varchar数据类型字段是 TOTAL并且 else 字段返回 numeric type 字段，因此这会导致错误。

要解决它，您需要删除 case 语句，然后它会根据您的需要正常工作。

select  
CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL'
            ELSE shipping_company END AS shipping_company,
CONVERT(numeric, (SUBSTRING(ship_date, 1, 6))) AS Date,
CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL'
            ELSE shipping_category END AS shipping_category,
sum(convert(decimal(10,2),shipping_cost))
from tbl
group by shipping_company, (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))), 
shipping_category WITH rollup

SQL FIDDLE :

| SHIPPING_COMPANY |   DATE | SHIPPING_CATEGORY | COLUMN_3 |
------------------------------------------------------------
|              DHL | 201201 |              CD'S |      6.5 |
|              DHL | 201201 |      Yearly TOTAL |      6.5 |
|              DHL | 201202 |              CD'S |      5.5 |
|              DHL | 201202 |      Yearly TOTAL |      5.5 |
|              DHL | 201203 |              CD'S |     46.5 |
|              DHL | 201203 |      Yearly TOTAL |     46.5 |
|              DHL | 201205 |              CD'S |      3.5 |
|              DHL | 201205 |      Yearly TOTAL |      3.5 |
|              DHL | 201303 |              CD'S |      3.5 |
|              DHL | 201303 |      Yearly TOTAL |      3.5 |
|              DHL | 201304 |              CD'S |        8 |
|              DHL | 201304 |      Yearly TOTAL |        8 |
|              DHL | 201305 |              CD'S |      4.5 |
|              DHL | 201305 |      Yearly TOTAL |      4.5 |
|              DHL | (null) |      Yearly TOTAL |       78 |
|              UPS | 201203 |              CD'S |      5.5 |
|              UPS | 201203 |           Records |      4.5 |
|              UPS | 201203 |             Tapes |      3.5 |
|              UPS | 201203 |      Yearly TOTAL |     13.5 |
|              UPS | 201303 |              CD'S |       18 |
|              UPS | 201303 |           Records |      4.5 |
|              UPS | 201303 |             Tapes |    12.44 |
|              UPS | 201303 |      Yearly TOTAL |    34.94 |
|              UPS | (null) |      Yearly TOTAL |    48.44 |
|             USPS | 201203 |              CD'S |       68 |
|             USPS | 201203 |      Yearly TOTAL |       68 |
|             USPS | 201302 |              CD'S |     36.5 |
|             USPS | 201302 |      Yearly TOTAL |     36.5 |
|             USPS | 201303 |              CD'S |       37 |
|             USPS | 201303 |      Yearly TOTAL |       37 |
|             USPS | (null) |      Yearly TOTAL |    141.5 |
|            TOTAL | (null) |      Yearly TOTAL |   267.94 |

所以现在你必须忍受日期列中的空值。希望这能解答你的疑惑。

希望这有帮助......

关于sql - 每月报告的 TSQL Sum by group，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16454370/

文章推荐： php - 重定向到 php 中的数据 url

文章推荐： gnuplot - 删除 gnuplot 中右上角的直方图文本

文章推荐： linux-kernel - Android (Linux) 内核 'HZ' 变量

c++ - Eigen sum()、colwise().sum().sum() 和 rowwise().sum().sum() 都给出了不同的答案
我有这个示例代码: #include #include int main() { Eigen::MatrixXf M = Eigen::MatrixXf::Random(1000, 1000)
sum - 当 sum() 一列时，我收到此错误 AttributeError : 'DataFrame' object has no attribute 'sum'
我有一个像这样的数据框: +-----+--------+ |count| country| +-----+--------+ | 12| Ireland| | 5|Thailand| +-
带条件的字段的 MySQL SUM，IF(SUM(bill_1) IS NULL, '99' , SUM(bill_1)) AS tot_bill_1
我想要 SUM(tot_bill_1+tot_bill_2) AS 总计，但这不起作用 SELECT *, IF(SUM(bill_1) IS NULL, '99', SUM(bill_1)) AS
performance - matlab sum(X-Y) vs sum(X) - sum(Y)
如果我们有两个矩阵 X 和 Y，都是二维的，现在在数学上我们可以说:sum(X-Y)=sum(X)-总和(Y). Matlab 哪个效率更高？哪个更快？最佳答案在我的机器上，sum(x-y) 对于
sql - 为什么 SUM(a + b) != SUM(a) + SUM(b) 在 Hive 中？
我正在运行 Hive 1.1.0 并看到对于两个 bigint 列，active_users 和 inactive_users，SUM(active_users + inactive_users) <
Mysql SUM 的 SUM
是否可以在一个选择查询中求和？类似这样的事情: SELECT id, SUM(current_price - bought_price)*amount AS profit FROM purchase
sql - 为什么 SUM(COL1 + COL2) 和 SUM(COL1) + SUM(COL2) 会产生不同的结果？
这是一个相当奇怪的结果。我希望这些具有相同的产量。下面还有从数据库中提取的 excel 链接。 https://twentius.opendrive.com/files?89038281_muoyg
tsql - 在 TSQL 中，SUM(A + B) 与 SUM(A) + SUM(B) 之间是否存在性能差异？
我必须对 2 个字段求和，然后再求和。从性能的角度来看，先添加字段还是在对列求和之后添加字段有什么区别？方法 1 = SELECT SUM(columnA + columnB) 方法 2 = SEL
arrays - 在这些条件下，是否有可能比 O(n^2) 更好地执行 3-sum/4-sum...k-sum？ - 技术面试
这是一个经典问题，但我很好奇是否有可能在这些条件下做得更好。问题:假设我们有一个长度为4*N的排序数组，即每个元素重复4次。请注意，N 可以是任何自然数。此外，数组中的每个元素都受制于 0 A. 执
sum - PIG : sum and division, 创建对象
我正在编写一个 Pig 程序，该程序加载一个用制表符分隔整个文件的文件例如:名称 TAB 年份 TAB 计数 TAB... file = LOAD 'file.csv' USING PigStora
sql-server - SUM 一列然后减去另一列的 SUM
我有一个包含以下字段的表: EmpID, Code, Amount, TransDate, CM, CMDate 我想要进入数据网格的是 SUM所有的Amount具有相同的 Code和 SUM CM具
php - 在一个查询中显示月 SUM 和年 SUM
我有两个单独的查询用于提取报告信息。一年效果很好。但是，如果一个月超过 1 年，则不会显示正确的响应。这是我的两个查询: select SUM(rpt_complete.total) total,
MySQL SUM(列) + (SUBQUERY WITH SUM)
我想查询一个团队的积分。通过在列上执行 SUM + 来自具有相同团队 ID 的另一个表的 SUM 来添加这些点。我试着这样写: SELECT k.id, s.fylke, s.
mysql - SUM - 为什么我在使用 sum 和使用计算器时得到不同的总和)？
这个问题在这里已经有了答案: How to deal with floating point number precision in JavaScript? (47 个回答) Unexpected
algorithm - 为什么 sum(x/y)/n 不等于 sum(x)/sum(y) 其中 x 和 y 是 n 个正整数的向量？
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。这个问题似乎与 help center 中定义的范围内的编程无关。 . 关闭 5 年前。 Improve
SQL Sum of last X Sum of records
我已经找了一段时间，但找不到这个问题的答案(也许我没有搜索正确的术语或其他东西)。基本上，我有一个数据库，每个日期有任意数量的条目。我需要取包含条目的最后 X 天的总和(忽略没有条目的天数)。我知道如
google-sheets - 在谷歌电子表格查询中使用 sum 会导致许多单元格中的单词 "sum"
我正在尝试获取 B 行中包含 A 行中某个值的所有值中的一些值。我猜这个问题很简单。这是我的查询: =QUERY('Sheet1'!$A$16:D, "Select sum(D) Where C c
mysql - SQL 查询语法错误 : unexpected 'SUM' (sum)
我正在尝试运行以下查询，但出现以下错误: You have an error in your SQL syntax; check the manual that corresponds to your
php - MySQL 中的行明智 SUM 与列明智 SUM
我有一个 tableA，其中包含以下结构我将此结构修改为如下所示的tableB，以减少行数，并且类别是固定长度的假设我在 tableA 中修改为新结构后有 210 万条数据，tableB 仅包含
sql - 每行的 SUM 和过滤结果 SUM IN $x
我的表在 Postgres 中的数据: id user_id sell_amount sell_currency_id buy_amount buy_currency_id type

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 每月报告的 TSQL Sum by group