How to count values in SQLITE in more then one column?-CodePudding

I like to count the number of appearance of my users in more then one column. This is how I count the values in columns:

SELECT COUNT(col_01) FROM table_01 WHERE col_01 = 'John'     // result: 2
SELECT COUNT(col_02) FROM table_01 WHERE col_02 = 'John'     // result: 4

Is there any way to count John in one step in all columns I have and get the results 6?

Thank you in advance!

CodePudding user response：

First filter the table so that you get only the rows with 'John' in any of the 2 columns and then use aggregate function TOTAL() on each column:

SELECT TOTAL(col_01 = 'John')   TOTAL(col_02 = 'John') AS total
FROM table_01 
WHERE 'John' IN (col_01, col_02);

CodePudding user response：

Try conditional aggregation

select 
  sum(case when col_01='John' then 1 end)  
  sum(case when col_02='John' then 1 end)
from table_01;