How to perform a sum on rows in a temp table and have the total row at the top as a rollup?

Question

I have a temp table that I insert 10 rows into.

They look like this:

COL1    COL2            COL3
Jobs    ALL SALARIES    AVG SALARIES
19      $2,286,232.00   $285,779.00 -- THIS IS THE SUMMARY ROW AT THE TOP
0       0               0
1       $386,156.00     $96,539.00
2       $923,520.00     $153,920.00
1       $626,200.00     $469,650.00
7       $2,772,400.00   $440,000.00
0       0               0
3       $2,203,200.00   $61,200.00
1       $153,000.00     $102,000.00
3       $1,752,816.00   $213,783.00
1       $408,000.00     $204,000.00

Row one is the ROLL UP row and everything needs to be in this order, but, the 1st row is the total row and as you can see, it's not correct.

ROW 1 should be THIS:

19      $11,682,164.00  $217636.50

I'm performing a SUM(CONVERT(numeric(18,2),col2)) OVER() as col2 and AVG(CONVERT(numeric(18,2),col3)) as col3

But as you can see, it's not correct.

NOTE: the $ and , are NOT there in the original SQL and I only put it there for clarity.

Finally, here's the SQL that produces the above, quasi-correct...

    select TOP(9) PW_WAGE_LEVEL,
        CONVERT(numeric(18,2),CASE WHEN WAGE_RATE_OF_PAY_TO = '0.00' THEN
            CASE WHEN ANNUALIZED_SALARY = '0.00' THEN
                CASE WHEN pw_unit_of_pay = 'year' THEN
                    CAST(REPLACE(WAGE_RATE_OF_PAY_FROM,',','') as numeric)
                END
            ELSE
                CAST(REPLACE(MIDPOINT_WAGE_RATE,',','') as numeric)
            END
        ELSE 
            CASE WHEN WAGE_RATE_OF_PAY_TO = '0.00' THEN
                CASE WHEN pw_unit_of_pay = 'hour' THEN
                    CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                ELSE
                    CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                END
            ELSE 
                CASE WHEN WAGE_RATE_OF_PAY_TO > '0.00' THEN
                    CASE WHEN pw_unit_of_pay = 'hour' THEN
                        CASE WHEN ANNUALIZED_SALARY = '0.00' THEN
                            CAST(REPLACE(MIDPOINT_WAGE_RATE,',','') as numeric)
                        ELSE
                            CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                        END
                    END
                END
            END
        END * pw_wage_level) as ttlWagesPerLevel,
        SUM(CONVERT(numeric(18,2),CASE WHEN WAGE_RATE_OF_PAY_TO = '0.00' THEN
            CASE WHEN ANNUALIZED_SALARY = '0.00' THEN
                CASE WHEN pw_unit_of_pay = 'year' THEN
                    CAST(REPLACE(WAGE_RATE_OF_PAY_FROM,',','') as numeric)
                END
            ELSE
                CAST(REPLACE(MIDPOINT_WAGE_RATE,',','') as numeric)
            END
        ELSE 
            CASE WHEN WAGE_RATE_OF_PAY_TO = '0.00' THEN
                CASE WHEN pw_unit_of_pay = 'hour' THEN
                    CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                ELSE
                    CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                END
            ELSE 
                CASE WHEN WAGE_RATE_OF_PAY_TO > '0.00' THEN
                    CASE WHEN pw_unit_of_pay = 'hour' THEN
                        CASE WHEN ANNUALIZED_SALARY = '0.00' THEN
                            CAST(REPLACE(MIDPOINT_WAGE_RATE,',','') as numeric)
                        ELSE
                            CAST(REPLACE(ANNUALIZED_SALARY,',','') as numeric)
                        END
                    END
                END
            END
        END) * pw_wage_level) OVER() as ttlWages,
        SUM(pw_wage_level) OVER() as ttlPTFTJobs
from myFirstTempTable
where worksite_state = 'RI'
and JOB_TITLE like '%accountant%'
group by pw_unit_of_pay,pw_wage_level,ANNUALIZED_SALARY

UPDATE:

Actual SCHEMA is coming in like this to the TEMP TABLE:

USE [myFirstTempDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_call_homepage_report]    Script Date: 1/9/2017 10:07:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_call_homepage_report]        
    @wsCity varchar(255),
    @wsState varchar(10),
    @pwUOP varchar(10),
    @jobTitle varchar(255)

    AS
        BEGIN   

        --CREATE A TEMP TABLE first... we need somewhere to stick the data
        IF OBJECT_ID ('tempdb..#tempHomePageResults') is not null
        drop table #tempHomePageResults

        DECLARE @tempHomePageResults TABLE (
            TotalNbrPTandFTJobs varchar(50), 
            TotalAnnualWages varchar(50), 
            TotalAvgSalary varchar(50))

                INSERT INTO @tempHomePageResults
                execute usp_row1_homePageReport @wsCity,@wsState,@pwUOP,@jobTitle
                --BLANK ROW - FULL TIME SPECIALTY JOBS...
                INSERT INTO @tempHomePageResults
                (TotalNbrPTandFTJobs, TotalAnnualWages,TotalAvgSalary)
                VALUES(1,0,0)
                --SKILL LEVEL ROWS!
                --FT JOB SKILL LEVEL ROWS!              
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'Y',1
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'Y',2
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'Y',3
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'Y',4
                --BLANK ROW - PART TIME SPECIALTY JOBS...
                INSERT INTO @tempHomePageResults
                (TotalNbrPTandFTJobs,
                TotalAnnualWages,TotalAvgSalary)
                VALUES(2,0,0)
                --SKILL LEVEL ROWS!
                --PT JOB SKILL LEVEL ROWS!              
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'N',1
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'N',2
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'N',3
                INSERT INTO @tempHomePageResults
                execute usp_row1_6_HomePageReport @wsCity,@wsState,@pwUOP,@jobTitle,'N',4                           

                SELECT * FROM @tempHomePageResults

END 

NOTE: I understand that this is "not" normalized but it's a quick import from CSV to SQL SERVER and there's a TIME CRUNCH.

Probably would be better suited to normalize the correct datatypes as:

INT NUMERIC(18,2) NUMERIC(18,2)

ASSUME the NORMALIZED data will be thus:

COL1    COL2            COL3
Jobs    ALL SALARIES    AVG SALARIES
19      2286232.00      285779.00 -- THIS IS THE SUMMARY ROW AT THE TOP
0       0               0
1       386156.00       96539.00
2       923520.00       153920.00
1       626200.00       469650.00
7       2772400.00      440000.00
0       0               0
3       2203200.00      61200.00
1       153000.00       102000.00
3       1752816.00      213783.00
1       408000.00       204000.00

Hope this helps you help me.

Thanks

Answer 1

Here's my answer: It's easy enough to put the sum row at the top. The question will be: how will the other rows appear? Nothing in your temp table will allow us to produce any reliable sort for the rest of the rows. Remember, just because you insert rows in a given order, you cannot expect them to come out in the same order. Which we would be a simple

ORDER BY TotalAnnualWages DESC

But I think you want the other rows in the order you insert them. So, I would add an identity column to your table:

CREATE TABLE dbo.tempHomePageResults  (
        ID INTEGER IDENTITY(1,1) PRIMARY KEY ,
        TotalNbrPTandFTJobs INTEGER, 
        TotalAnnualWages NUMERIC(12,2), 
        TotalAvgSalary NUMERIC(12,2))

With that done, the following will reutrn the max row first and then every row afterword

WITH CTE AS 
 (SELECT res.TotalNbrPTandFTJobs, res.TotalAnnualWages, res.TotalAvgSalary, 
 ROW_NUMBER() OVER(ORDER BY res.TotalAnnualWages DESC) AS RN,
 res.ID
 FROM dbo.tempHomePageResults res
 )
SELECT cte.TotalNbrPTandFTJobs, cte.TotalAnnualWages, cte.TotalAvgSalary
  FROM CTE
ORDER BY CASE WHEN cte.RN =1 THEN RN ELSE 2 END, 
  cte.ID

So, this way the max row (RN =1) comes first, every other row in the order you inserted them.

UPDATE FOR ADAM's ANSWER:

UPDATE FOR ADAM's LAST COMMENT and mine regarding a -- Windowed functions can only appear in the SELECT or ORDER BY clauses.

THIS STATEMENT APPEARS "after" the last INSERT into the @tempHomePageResults table and before the final "END" statement. (SEE CODE ABOVE)

            BEGIN
                UPDATE @tempHomePageResults SET
                    TotalAnnualWages = CAST(SUM(CONVERT(numeric(18,2),TotalAnnualWages)) OVER() as varchar),
                    TotalAvgSalary = AVG(CAST(CONVERT(numeric(18,2),TotalAvgSalary) as varchar))
                WHERE h1bID = 0
            END