Calculating sum of row into a column-CodePudding

I'm a bit confused on why does my number not add to each other but they seem to just stack together. I did some research and tried to cast my data into numbers but doesn't seem to give me the outcome I want. Any ideas? Picture Provided are the examples of my results when executed. The Other is the data in Table. [MS SQL 2019]

Result1 Table Data

--This is what I came up with so far (Note: I have more columns to add in 100 )--

select PartNumber, sum(cast(a01b   a02a as int)) 
as total
from bincount
group by PartNumber

CodePudding user response：

You need to check the data type of both columns, and whether they are nullable, because NULL 17 will give you NULL, not 17.

Sum many rows with NULLs in them, and the sum will be way less than what you'd expect.

You might be forced to write using COALESCE

SELECT SUM(
         COALESCE(a20a, 0)   COALESCE(a20b, 0)
       ) AS total

or, but this should not be necessary,

         COALESCE(CAST(a20a AS INT), 0)   COALESCE(CAST(a20b AS INT), 0)

Or you can add after the SUM, which is faster:

SELECT COALESCE(SUM(a20a),0)   COALESCE(SUM(a20b),0)

Now the NULL values will be ignored (NULL 5 10 is NULL, but SUM(5, NULL, 10) is 15), unless a whole column is entirely made of NULLs (in this case, SUM returns NULL and not 0); the COALESCE tackles this edge case. If you are sure you have at least one non-NULL in each column, you can do without the COALESCE.

CodePudding user response：

Mysql query - calculate file id count

select file_id , count(ID) from table group by file_id ORDER BY ID DESC limit 30;