Completing calculations in temp table and returning those results in a second query

Question

Fiddle - http://sqlfiddle.com/#!18/c2b80/17

Tables:

CREATE TABLE [OrderTable] 
(
    [id] int,
    [OrderGroupID] int,
    [Total] int,
    [fkPerson] int,
    [fkitem] int
    PRIMARY KEY (id)
) 

INSERT INTO [OrderTable] (id, OrderGroupID, Total ,[fkPerson], [fkItem]) 
VALUES
  ('1', '1', '20', '1', '1'),
  ('2', '1', '45', '2', '2'),
  ('3', '2', '32', '1', '1'),
  ('4', '2', '30', '2', '2');

CREATE TABLE [Person] 
(
    [id] int,
    [Name] varchar(32)
    PRIMARY KEY (id)
) 

INSERT INTO [Person] (id, Name) 
VALUES ('1', 'Fred'),
       ('2', 'Sam');

CREATE TABLE [Item] 
(
    [id] int,
    [ItemNo] varchar(32),
    [Price] int
    PRIMARY KEY (id)
) 

INSERT INTO [Item] (id, ItemNo, Price) 
VALUES ('1', '453', '23'),
       ('2', '657', '34');

Original query:

WITH TABLE1 AS 
(
    SELECT 
        -- P.ID AS [PersonID], 
        -- P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        OrderTable OT
    -- INNER JOIN Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        -- P.ID, P.Name,
        i.id, ot.fkperson
)
SELECT 
    t1.fkperson,
    t1.[itemid],
    t1.[total],
    t1.[rownum]
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson

I have attempted to complete the sum function on a column in a temp table and join it back to the CTE. It either errors or I get the incorrect columns. The idea for this is to perform calculations in a temp table to improve performance of queries. How can I join a sum()'d column from a temp table to the original table and output the results?

Current query:

CREATE TABLE #ot
(
     fkperson int, 
     Total int
)

INSERT INTO #ot
    SELECT 
        fkperson, 
        SUM(total) AS [Total]
    FROM 
        OrderTable
    GROUP BY 
        [fkperson]

WITH TABLE1 AS 
(
    SELECT 
        ot.[Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        #ot OT
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        i.id, ot.fkperson
)
SELECT 
    t1.fkperson,
    t1.[itemid],
    t1.[total],
    t1.[rownum],
    p.[Name],
    Totalrows = (SELECT MAX(rownum) FROM TABLE1),
    totalrows = @@ROWCOUNT
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson

@Ryan . . . I really have no idea what you are asking. What does this mean: "complete the sum function on a column in a temp table "? Why do you need a temp table? What are you really trying to do? — Gordon Linoff, Sep 24 '18 at 13:49
@GordonLinoff basically i want to calculate the sum and group by in the temp table and then join this back to table1 where i can return the [total] column from the temp table without having the sum it in the table1 CTE. does this make sense? — Ryan Gadsdon, Sep 24 '18 at 16:01
@RyanGadsdon . . . You wouldn't use temporary tables for this, unless you had a reason to keep the information around between queries. That is why sample data and desired results are so helpful. — Gordon Linoff, Sep 24 '18 at 16:09

score 1 · Answer 1 · answered Sep 24 '18 at 16:12

If I understand correctly (based on the comment), you would do:

WITH TABLE1 AS (
      SELECT P.ID AS [PersonID], P.Name, 
             SUM(OT.[Total]) AS [Total], 
             i.[id] AS [ItemID],
             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
             COUNT(*) OVER () as cnt
             ot.fkperson
      FROM OrderTable OT INNER JOIN
           Person P
           ON P.ID = OT.fkperson INNER JOIN 
           Item I
           ON I.[id] = OT.[fkItem]
      GROUP BY P.ID, P.Name, i.id, ot.fkperson
     )

I see no use for a temporary table, unless you want to persist the results between queries.

score 0 · Answer 2 · edited Feb 11 '20 at 18:44

I might be wrong, but I think you're trying to establish the total number of rows in the #ot table and then attribute that to each row for the output in the last select statement. So each row has the maximum number of rows in the table.

If that's right then I think the following changes I made to your original code does that:

CREATE TABLE #ot
(
 fkperson int, 
 [fkItem] int,  --LS23 added to make code run
 Total int
)

INSERT INTO #ot
SELECT 
    fkperson, 
    [fkItem], --LS23 added to make code run
    SUM(total) AS [Total]
FROM 
    [#OrderTable]
GROUP BY 
    [fkperson],
    [fkitem]; --LS23 added to make code run

Then the following method of populating TotalRows in the final select statement

Totalrows = (SELECT MAX(rownum) FROM TABLE1)

Does that help?

Completing calculations in temp table and returning those results in a second query

2 Answers2