CODEWITHSUNDEEP
sqlsqlitecase
S1M0N_H
S1M0N_H

Reputation: 139

SQLite -- summing values in a case expression within a select

I'm trying to get a sum from a case in line 4. I've tried wrapping the case in sum, having sum around s.qty, but nothing is working. 

select s.part,
max(case s.location when 'stock' then s.qty else 0 end) qty_stock,
max(case s.location when 'material' then s.qty else 0 end) qty_material,
case s.location when not 'stock' or 'material' then s.qty else 0 end qty_wip

from stock as s

group by s.part

output

Part        qty_stock qty_material qty_wip
"Part 1"    "10"      "25"         "0"
"Part 2"    "12"      "0"          "0"
"Part 3"    "14"      "0"          "0"
"Part 4"    "16"      "0"          "0"
"Part 5"    "0"       "0"          "0"
"Part 6"    "0"       "0"          "0"

Any help gladly received.

Upvotes: 1

Views: 988

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need sum() instead :

select s.part,
       sum(case when s.location = 'stock' then s.qty else 0 end) as qty_stock,
       sum(case when s.location = 'material' then s.qty else 0 end) as qty_material,
       sum(case when s.location not in ('stock', 'material') then s.qty else 0 end) as qty_wip
from stock as s
group by s.part;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I think you want:

select s.part,
       max(case s.location when 'stock' then s.qty else 0 end) as qty_stock,
       max(case s.location when 'material' then s.qty else 0 end) as qty_material,
       sum(case when s.location not in ('stock', 'material') then s.qty else 0 end ) as qty_wip
from stock as s
group by s.part;

Your problem is the case expression itself. Using a full condition should fix the issue.

Upvotes: 0

Related Questions