CALCULATE HOW MANY 1 IN A GIVEN TABLE-CodePudding

Hello all in SQL we have to used a query to get a desired output as we want. sometimes we have to write a small and large query to get a same output.

i have a problem statement where i want to calculate a count of 1 in following table:

i want to calculate a count of sum.

CodePudding user response：

With sample data you posted, one option is

SQL> with test (a, b, c, d) as
  2    (select 1, 0, 0, 0 from dual union all
  3     select 0, 1, 0, 0 from dual union all
  4     select 0, 0, 1, 0 from dual union all
  5     select 0, 0, 0, 1 from dual
  6    )
  7  select sum(a   b   c   d) result
  8  from test;

    RESULT
----------
         4

SQL>

CodePudding user response：

If your values are only 0 or 1 then:

SELECT SUM(A   B   C  D) AS total
FROM   table_name

If your values can be something else then:

SELECT SUM(
         CASE A WHEN 1 THEN 1 ELSE 0 END
         CASE B WHEN 1 THEN 1 ELSE 0 END
         CASE C WHEN 1 THEN 1 ELSE 0 END
         CASE D WHEN 1 THEN 1 ELSE 0 END
       ) AS total
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (a, b, c, d) AS
  SELECT 1, 0, 0, 0 FROM DUAL UNION ALL
  SELECT 0, 1, 0, 0 FROM DUAL UNION ALL
  SELECT 0, 0, 1, 0 FROM DUAL UNION ALL
  SELECT 0, 0, 0, 1 FROM DUAL;

Both output:

TOTAL

4

TOTAL
4

db<>fiddle here

CodePudding user response：

WITH CTE(A,B,C,D)AS
(
  SELECT 1,0,0,0 UNION ALL
  SELECT 0,1,0,0 UNION ALL
   SELECT 0,0,1,0 UNION ALL
  SELECT 0,0,0,1
)
SELECT SUM
(
  CASE 
     WHEN C.A=1 OR C.B=1 OR C.C=1 OR C.D=1 THEN 1
    ELSE 0
  END
)AS CNTT
FROM CTE AS C

You can try also this solution