Incorrect syntax error on SQL server

Question

I am no SQL expert but I can't see why I'm getting an error with the following code: http://sqlfiddle.com/#!3/2de76/7. Please can someone tell me what is wrong with this. Thanks.

DECLARE @total INT
SET @total = 0
SELECT
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total = @total
        WHEN s1.dayTotal > s2.dayTotal      THEN @total = @total + (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

The expected output:

rowCount,res,dayTotal,dayTotal
1,3,3,0
2,4,4,3
3,4,3,4
4,7,6,3

Are you attempting to get a running total in the @Total variable? — user5151179, Sep 18 '15 at 08:30

Mihai Ovidiu Drăgoi · Answer 1 · 2015-09-18T09:14:12.767

3

You're trying to use the CASE's THEN to alter the value of a variable. Doesn't work like that. This, however, works, and looks more close to what you might want to achieve:

DECLARE @total INT
SET @total = 0
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total
        WHEN s1.dayTotal > s2.dayTotal      THEN (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

PS - your CASE doesn't take the possibility of s1.dayTotal = s2.dayTotal into account. Not sure that's okay.

PPS - if you want the second WHEN to sum up the total AND the difference, use

WHEN s1.dayTotal > s2.dayTotal      THEN @total+(s1.dayTotal - s2.dayTotal)

PPPS - This seems to produce the desired resultset:

select a.RowCountID, case when b.diff > 0 then a.res+b.diff else a.res end as res, a.daytotal1, a.daytotal2  from
(
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
  ) a 
  left join
(
  SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
) b on a.rowcountID = b.rowcountID+1
order by a.rowcountID

Not sure what this kind of self-joining query is named, but it should have a name :)

Hope it works well!

edited Sep 18 '15 at 09:14

answered Sep 18 '15 at 08:27

Mihai Ovidiu Drăgoi

1,307
1
10
16

I think this misses the point of the OP's use of a variable, which is to sum together the totals of different rows. – Sep 18 '15 at 08:31
Sorry for a delay. What I would ultimately like is for res to be 3,4,4,7 - it must be a cumulative figure. – Ukuser32 Sep 18 '15 at 08:39
Yeah, that's what I meant with "sum together the totals of different rows" too. This answer still doesn't do that even with the edit. @Ukuser32 I think it would be clearer to other users if you could include the sample data and the expected output from your comment directly in your question. – Sep 18 '15 at 08:46
Done - I was just wanting the query to run not expecting us to fix it as well :) – Ukuser32 Sep 18 '15 at 08:48
Could you explain the res column? what do you want it to contain, exactly? – Mihai Ovidiu Drăgoi Sep 18 '15 at 08:58
It should contain the cumulative total of res (which therefore never decreases). My question now includes the desired results. res should never decrease because it is cumulative so for the 3rd row should be the previous figure but the final row should jump to 7 because the last @total would be 4 + the 3 difference on the final row. – Ukuser32 Sep 18 '15 at 09:00
Edited answer. Seems ok result-wise. – Mihai Ovidiu Drăgoi Sep 18 '15 at 09:14
The reason I'm apprehensive is there are like 3 joins which if I have a 600k rows will drastically slow this down. – Ukuser32 Sep 18 '15 at 09:30

score 1 · Answer 2 · edited Sep 18 '15 at 18:29

You need to remove "@total =" from the case statement.

DECLARE @total int
SET @total = 0

SELECT
  s1.RowCountID,
  CASE
    WHEN s1.dayTotal < s2.dayTotal THEN @total
    WHEN s1.dayTotal > s2.dayTotal THEN @total + (s1.dayTotal - s2.dayTotal)
  END AS res,
  s1.dayTotal,
  s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2
  ON s2.RowCountID = (s1.RowCountID - 1)
ORDER BY s1.RowCountID ASC

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

Ok after further investigation (and finding how to get cumulative sum), the following is the correct code to make it work (but with the addition of the case command):

SELECT
    s1.RowCountID,
      SUM(
      CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN 0
        WHEN s1.dayTotal > s2.dayTotal      THEN s1.dayTotal - s2.dayTotal
      END) OVER(
           ORDER BY s1.RowCountID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS res2,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

My actual issue is I also use MySQL (I was experimenting to see if it was possible elsewhere) so the next question is how this would look in MySQL?? That will take some further investigations!!

edited May 23 '17 at 11:53

Community

1
1

answered Sep 18 '15 at 09:38

Ukuser32

2,147
2
22
32

I considered answering with this already, except you put "SQL Server 2008" in your question title and this is only supported starting with SQL Server 2012. (Note: it does work on SQLFiddle's "MS SQL Server 2008" mode, but that isn't actually a 2008 version.) – Sep 18 '15 at 12:20
Ah! Maybe I should change the title. It was only because I was using SQL fiddle!! – Ukuser32 Sep 18 '15 at 12:55

Incorrect syntax error on SQL server

3 Answers3