CODEWITHSUNDEEP
sqlsqlite
DeFacto
DeFacto

Reputation: 103

SQLite Sum and CASE statement

I have a table

----------------------
| Col1 | Col2 | Col3 |
----------------------
| 1    |text1 | 1    |
----------------------
|  98  |text2 | 2    |
----------------------
| 2    |text3 | 1    |
----------------------
| 98   |text4 | 3    |
----------------------

I need to get a Sum of Col3 where Col1 = 98 and Sum of Col3 where Col1 <> 98. The desired result would be 5 and 2.

-------------------------------
| aShort (=98) | aLong (<>98) |
-------------------------------
|     5        |      2       |
-------------------------------

My SQL query looks like that:

'SELECT sum(case when Col1 = 98 then Col3 else 0 end) as aShort, ' +
  'sum(case when Col1 <> 98 then Col3 else 0 end) as aLong '...

And the result that i get is 0 and 7. What am i doing wrong?

Upvotes: 1

Views: 1219

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65343

I suppose data type of Col1 is not integer or numeric but of string type such as text or varchar, and seems values contain some whitespaces in them. I strongly recommend to hold a numeric value within a numeric type of column, but in the current case you need to cast as integer in order to get such a result as desired using the query below

SELECT SUM(CASE WHEN CAST(Col1 AS INT)  = 98 THEN Col3 ELSE 0 END) AS aShort, 
       SUM(CASE WHEN CAST(Col1 AS INT) <> 98 THEN Col3 ELSE 0 END) AS aLong
  FROM tab

Demo

Upvotes: 4

Related Questions