Shadow Fiend Shadow Fiend - 3 months ago 11
MySQL Question

Select SUM using Multiple Criteria in MYSQL

I have a Mysql Command that sums a data and Sad to say its a Varchar.

here is my command

SELECT SUM(replace(TotalAmount, ',', ''))
FROM physicalinv_final
WHERE ItemCode LIKE 'DRY%' AND
PINo = 'PI00000001'


The output of that is to sum up
TotalAmount
where
ItemCode
contains the word DRY and
PINo
= PI00000001

My question is like this I have also other criteria like this

BEV
DES
MEA
OTH
DRY
RIC
SEA


I try this code

SELECT SUM(replace(TotalAmount, ',', '')) FROM physicalinv_final where ItemCode like 'DRY%' ItemCode like 'BEV%' and So On ..... and PINo = 'PI00000001'


but the output is
null
although i have a data that contains DRY.

this the output i want

link

is there a way to fix this?

TYSM for help

Answer

Use or and group by instead:

SELECT ItemCode, SUM(replace(TotalAmount, ',', '')) 
FROM physicalinv_final 
WHERE (ItemCode like 'DRY%' OR 
      ItemCode like 'BEV%' OR ...) 
    AND PINo = 'PI00000001'
GROUP BY ItemCode

Or if you just want the overall sum containing any of those item codes, remove the group by clause:

SELECT SUM(replace(TotalAmount, ',', '')) 
FROM physicalinv_final 
WHERE (ItemCode like 'DRY%' OR 
      ItemCode like 'BEV%' OR ...) 
    AND PINo = 'PI00000001'