'Count flags for a variable (big) number of colums

I have a table which looks like this: http://i.stack.imgur.com/EyKt3.png

And I want a result like this:

Conditon    COL
ted1        4
ted2        1
ted3        2

I.e., the count of the number of '1' only in this case.

I want to know the total no. of 1's only (check the table), neglecting the 0's. It's like if the condition is true (1) then count +1.

Also consider: what if there are many columns? I want to avoid typing expressions for every single one, like in this case ted1 to ted80.

mysqlsqloraclepostgresqlsas


Solution 1:[1]

Using proc means is the most efficient method:

proc means data=have noprint;
var ted:; *captures anything that starts with Ted;
output out=want sum =;
run;

proc print data=want;
run;

Solution 2:[2]

Try this

select 
sum(case when ted1=1 then 1 else 0 end) as ted1,
sum(case when ted2=1 then 1 else 0 end) as ted2,
sum(case when ted3=1 then 1 else 0 end) as ted3
from table

Solution 3:[3]

In PostgreSQL (tested with version 9.4) you could unpivot with a VALUES expression in a LATERAL subquery. You'll need dynamic SQL.

This works for any table with any number of columns matching any pattern as long as selected columns are all numeric or all boolean. Only the value 1 (true) is counted.

Create this function once:

CREATE OR REPLACE FUNCTION f_tagcount(_tbl regclass, col_pattern text)
  RETURNS TABLE (tag text, tag_ct bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT
     'SELECT l.tag, count(l.val::int = 1 OR NULL)
      FROM ' || _tbl || ', LATERAL (VALUES '
       || string_agg( format('(%1$L, %1$I)', attname), ', ')
       || ') l(tag, val)
      GROUP  BY 1
      ORDER  BY 1'
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = _tbl
   AND    attname LIKE col_pattern
   AND    attnum > 0
   AND    NOT attisdropped
   );
END
$func$;

Call:

SELECT * FROM f_tagcount('tbl', 'ted%');

Result:

tag  | tag_ct
-----+-------
ted1 | 4
ted2 | 1
ted3 | 2

The 1st argument is a valid table name, possibly schema-qualified. Defense against SQL-injection is built into the data type regclass.

The 2nd argument is a LIKE pattern for the column names. Hence the wildcard %.

db<>fiddle here
Old sqlfiddle

Related:

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 Reeza
Solution 2 Madhivanan
Solution 3

Related Questions