'CALCULATE HOW MANY 1 IN A GIVEN TABLE

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:

enter image description here

i want to calculate a count of sum.

sqloracle


Solution 1:[1]

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

db<>fiddle here

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0
Solution 2 Sergey

Related Questions