Count flags for a variable (big) number of colums

Question

I have a table which looks like this: https://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.

MySQL or Oracle? (Different products, with different versions of SQL.) And why the sas tag? — jarlh, Jul 02 '15 at 09:34
im working on sas as well as proc sql in SAS so i thought if some one could provide an answer in either would be great — 1SS2290, Jul 02 '15 at 09:56
This really is very basic SAS, you don't appear to have made any effort in attempting it first. I suggest you look up `proc means`, have a go at getting the answer, then post a new question if you have any specific problems — Longfish, Jul 02 '15 at 10:46
Lalit was being sarcastic. We expect you to provide the actual RDBMS you are working with, not an arbitrary enumeration of popular systems. The solution depends on the RDBMS. — Erwin Brandstetter, Jul 02 '15 at 12:55
Why the insistence on SQL? Proc means will handle in a jiffy. — Reeza, Jul 02 '15 at 13:09

score 1 · Answer 1 · answered Jul 02 '15 at 15:44

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;

answered Jul 02 '15 at 15:44

Reeza

20,510
4
21
38

score 0 · Answer 2 · answered Jul 02 '15 at 09:41

0

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

answered Jul 02 '15 at 09:41

Madhivanan

13,470
1
24
29

what if i have 10-20 variables i.e Ted1-Ted20 – 1SS2290 Jul 02 '15 at 09:43
You may need to repeat that for all columns or Use PIVOT – Madhivanan Jul 02 '15 at 09:46
repeating for all the columns is not an options because i could have as many as 80 columns for a table so – 1SS2290 Jul 02 '15 at 09:58
i tried macros combined with arrays to get the answer but not satisfied as its still not efficient.. remember im working on SAS and Procsql – 1SS2290 Jul 02 '15 at 10:00

Erwin Brandstetter · Answer 3 · 2022-03-28T00:26:22.657

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}

Count flags for a variable (big) number of colums

3 Answers3