Sum Over SQL without grouping or ordering

Question

I have a SQL Server query like this:

CREATE TABLE ##Temp(
    OrderID NVARCHAR(100), 
    ID INT,
    Prices INT,
    Total INT
);

INSERT INTO ##Temp (ID, Prices, OrderID, Total)
SELECT  fc.ID, f.Prices, f.OrderID, 
        (SUM(f.Prices) OVER()) AS Total 
FROM FruitCrates fc
LEFT JOIN Fruits f ON fc.ID = f.FruitCrateID
WHERE  fc.OrderID LIKE '18_1635' 
  AND fc.Rights = 1 
  AND fc.Cancelled = 0 
  AND f.OrderID IS NOT NULL;

SELECT * FROM ##Temp;

But I keep getting an error:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'Total'.

Which I think is because I am not using OVER() properly BUT I am not sure how to fix it.

PLEASE NOTE

SELECT statement works on its on when not put in after INSERT.

Results without INSERT

╔═════╦════════╦══════════╦═══════╗
║ ID  ║ Prices ║ OrderID  ║ Total ║
╠═════╬════════╬══════════╬═══════╣
║  77 ║      1 ║ 1_370    ║   104 ║
║  19 ║    101 ║ 1811_171 ║   104 ║
║  77 ║      2 ║ 1811_171 ║   104 ║
╚═════╩════════╩══════════╩═══════╝

Global temp table. I bet it has different schema. Please post result of `SELECT * FROM ##temp` before making insert — Lukasz Szozda, Jan 14 '16 at 11:29
You don't get the point. I guess that you have name colision(stored procedure call another stored procedure) and you use the same name for temp table. Without posting more code your case cannot be reproduced and should be closed. The answer is do not use the same name everywhere. — Lukasz Szozda, Jan 14 '16 at 11:32
Why do you hesitate to show metadata of `##temp`? When you decide to post all code and [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) please let me know. — Lukasz Szozda, Jan 14 '16 at 11:35
I agree with @lad2025 - try doing `DROP TABLE ##Temp` right at the top (though you might delete something you wanted...) or replace `##Temp` with another name (`#Temp1` will probably do). If either of those works, then that was your problem. — aucuparia, Jan 14 '16 at 11:44
Does sql server allow an alias identical to the table column on insert? — Mihai, Jan 14 '16 at 11:45
What I don't understand is why the `CREATE TABLE` statement didn't throw an error? — aucuparia, Jan 14 '16 at 11:58
@PleaseTeach Stored procedure (outer) create temp table, then you call other stored procedure and it also create temp table. Is that your case? — Lukasz Szozda, Jan 14 '16 at 12:04

Lukasz Szozda · Accepted Answer · 2016-01-14T13:23:45.280

Error message with insert Invalid column name 'Total'. indicates clearly that ##Temp table has different schema than column list in INSERT INTO (...) statement.

To troubleshoot it you should always check metadata(column names) right before insert:

SELECT *
FROM ##Temp
WHERE 1=2;

Then you could easily spot that it is different that what you've assumed to be.

Now you are using global temp table, it will live as long as last connection that refer to it is active. You should consider using local temp tables.

To solve your case you could:

Use different name to avoid collision

Right before you could (if not used in nested procedures):

IF OBJECT_ID('tempdb..##temp') IS NOT NULL     
DROP TABLE ##temp

CREATE TABLE ##temp...

There is also one nasty case when stored procedure called another stored procedure and both (outer and inner) creates temp table with the same name. You should avoid it.

CREATE PROCEDURE #outer
AS
BEGIN
   CREATE TABLE ##temp (id INT, guid UNIQUEIDENTIFIER, col2 VARCHAR(100));
   INSERT INTO ##temp VALUES(1,NEWID(), 'a');
   SELECT * FROM ##temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE ##temp (id INT, total INT);  -- no error during creation

    INSERT INTO ##temp(id, total)
    SELECT 2, 10;

    SELECT * FROM ##temp;
END
GO

EXEC #outer
-- Invalid column name 'total'.

LiveDemo

The funniest thing is when you use local temp tables with the same number of columns(or defaults) and implicit casts are possible, it will pass:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 varchar(10));
   INSERT INTO #temp VALUES(1, 'a');
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

LiveDemo2

And example with incompatible types:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 UNIQUEIDENTIFIER);
INSERT INTO #temp VALUES(1, NEWID());
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

Operand type clash: int is incompatible with uniqueidentifier

@aucuparia `What I don't understand is why the CREATE TABLE statement didn't throw an error? ` Please see my demo — Lukasz Szozda, Jan 14 '16 at 12:54
@lad2015, I ended up making my query as nested select query which eliminated the need of creating a temporary table, I will use these select statements within a view, I may post them in code review in future when completed, thanks for your detailed answer +1 — Mathematics, Jan 14 '16 at 13:13
@PleaseTeach Great to hear it. When you finish it please post a link to review using http://codereview.stackexchange.com/ and prepare live demo with http://sqlfiddle.com :) — Lukasz Szozda, Jan 14 '16 at 13:15

Sum Over SQL without grouping or ordering

1 Answers1