I need select values from a table and returns the total hours for all categories and their occurrences. The challenge is that there are different totals for each occurrence.

My query:

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

returns the following results:

The total is calculated based on the number of occurrences. The first one is totaled then for each additional occurrence, the HrsAddlOccur is used.

My final result should be (24 16) (1 0.5 0.5 0.5) 10 for a grand total of 52.5.

How do I loop and process the results to total this up?

CodePudding user response：

The total is calculated based on the number of occurrences. The first one is totaled then for each additional occurrence, the HrsAddlOccur is used.

SQL databases understand arithmetic. You can perform the computation on each row. As I understand, the logic you want is:

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

Later on you can aggregate the whole resultset to get the grand total:

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

CodePudding user response：

you can sum them simply up

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

it would do it like the example

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

fiddle