I have a table with stocks of different warehouse columns are there. I need to find out the records matching the below criteria.Its a mysql database.
Count number of records with sum of multiple columns equal to 0.
Structure of table
Stock A | Stock B | Stock C | Stock D
0 | 1 | 0 | 0
0 | 0 | 0 | 0
1 | 1 | 1 | 1
Here the output will be 1.
This should be fairly simple:
create table test (stock_a int, stock_b int, stock_c int, stock_d int);
insert into test values (0,1,0,0), (0,0,0,0), (1,1,1,1);
select count(*) from test where stock_a + stock_b + stock_c + stock_d = 0
Result:
count(*)
--------
1
Sum of multiple columns are in the where clause so the Count(1) only counts where summ of all fields is zero.
Select Count(1) From warehouse
Where (`Stock A` + `Stock B` + `Stock C` + `Stock D`) = 0
I think you won't have negative values in yourtable, then you can just do this:
SELECT COUNT(*) As cnt
FROM yourtable
WHERE (Stock_A, Stock_B, Stock_C, Stock_D) = (0, 0, 0, 0)