如何根据发生次数将多个SQL列中的值相加

Question

我需要从表中选择值，并返回所有类别及其出现次数的总小时数。挑战在于，每次事件的总数都不同。

我的查询：

SELECT c.Category,
c.HrsFirstOccur,
c.HrsAddlOccur,
COUNT(*) AS Occurrences
FROM dbo.Categories sc
INNER JOIN dbo.Categories c 
ON sc.CategoryID = c.CategoryID
INNER JOIN dbo.OrderHistory oh 
ON sc.GONumber = oh.OrderNumber
AND sc.Item = oh.ItemNumber
WHERE sc.BusinessGroupID = 1
AND oh.OrderNumber = 500
AND oh.ItemNumber = '100'
GROUP BY c.Category, c.HrsFirstOccur, c.HrsAddlOccur

返回以下结果：

<2>>

类别	HrsFirst发生	HrsAdd发生	发生次数
惯性	24	16
灯	1	0.5	4
人工	10	1

总数是根据出现次数计算的。第一个是合计的，然后对于每个额外的出现，使用HrsAddlOccr。

SQL数据库了解算术。您可以对每一行执行计算。据我所知，你想要的逻辑是：

SELECT 
c.Category,
c.HrsFirstOccur,
c.HrsAddlOccur,
COUNT(*) AS Occurrences,
c.HrsFirstOccur + ( COUNT(*) - 1 ) * HrsAddlOccur As Total
FROM ... < rest of your query > ..

稍后，您可以聚合整个结果集以获得总计：

SELECT SUM(Total) GrandTotal
FROM (
... < above query > ..
) t

您可以简单地将它们相加

WITH CTE as(SELECT c.Category,
c.HrsFirstOccur,
c.HrsAddlOccur,
COUNT(*) AS Occurrences
FROM dbo.Categories sc
INNER JOIN dbo.Categories c ON sc.CategoryID = c.CategoryID
INNER JOIN dbo.OrderHistory oh ON sc.GONumber = oh.OrderNumber
AND sc.Item = oh.ItemNumber
WHERE sc.BusinessGroupID = 1
AND oh.OrderNumber = 500
AND oh.ItemNumber = '100')
SELECT SUM(HrsFirstOccur + (CAST((Occurrences -1) AS DECIMAL(8,2)) * HrsAddlOccur)) as total FROM CTE

它会像那样做

CREATE TABLE CTE
([Category] varchar(7), [HrsFirstOccur] int, [HrsAddlOccur] DECIMAL(8,2), [Occurrences] int)
;

INSERT INTO CTE
([Category], [HrsFirstOccur], [HrsAddlOccur], [Occurrences])
VALUES
('Inertia', 24, 16, 2),
('Lights', 1, 0.5, 4),
('Labor', 10, 0, 1)
;

3 rows affected

SELECT SUM(HrsFirstOccur + (CAST((Occurrences -1) AS DECIMAL(8,2)) * HrsAddlOccur)) as total
FROM CTE

total
52.5000