1

I have a table with five tinyint columns.

If I make a query searching for a row, I get a result like this:

select flag1, flag2, flag3, flag4, flag5 from mytable where whatever;

Result set (image)

What I need is to get the sum as if they were integers. In this example, the output are four ones and one zero, so I'd like to get a 4.

This query does not work:

select sum(flag1, flag2, flag3, flag4, flag5) from mytable where whatever;

This doesn't either:

select sum(flag1, flag2, flag3, flag4, flag5) from mytable where whatever;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
Uxio
  • 33
  • 4

4 Answers4

3

You can use the + operator to sum up the values of a row.

SELECT flag1 + flag2 + flag3 + flag4 + flag5
       FROM mytable;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • You probably want this sticky bit answer OR the mankowitz answer. The point is that SUM() works across many rows of data. The "+" adds numbers in the same row. – Sql Surfer Jul 06 '18 at 00:17
  • Make sure you test this scenario .. 255 + 255 + 255 + 255 + 255 so you can see if you are going to need to cast up to an int from tiny int. – Sql Surfer Jul 06 '18 at 00:23
  • Also Make sure you really didn’t want this … flag1 | flag2 | flag3 | flag4 |flag5 aka. BITWISE OR (mySql might be “.|.” for bitwise OR) – Sql Surfer Jul 06 '18 at 00:24
2
select sum(flag1 + flag2+ flag3+ flag4+ flag5) sumflag  from mytable

Result:

| sumflag |
|---------|
|       4 |

Test Demo

DDL:

CREATE TABLE mytable
    (`flag1` tinyint, `flag2` tinyint, `flag3` tinyint, `flag4` tinyint, `flag5` tinyint)
;

INSERT INTO mytable
    (`flag1`, `flag2`, `flag3`, `flag4`, `flag5`)
VALUES
    (1, 1, 1, 0, 1)
;
Wei Lin
  • 3,591
  • 2
  • 20
  • 52
1

You have to cast tinyints into ints to use SUM(). Try this:

select sum(cast (flag1 as int),cast (flag2 as int) ...
mankowitz
  • 1,864
  • 1
  • 14
  • 32
1

select sum(flag1+flag2+flag3+flag4+flag5 ) as total,id from mytable group by id

honey
  • 53
  • 2