Multiple Count with Multiple column

Question

I am new in sql. I want to count something like:

Select count(*) from table where col1= x and col2=x and Col3=x.

I need to count the same value in all different column. Any help will be appreciated.

Barbaros Özhan · Accepted Answer · 2019-01-06 18:10:28Z

2

You can use conditional aggregation :

Select sum(case when col1='x' then 1 else 0 end) as count_col1,
       sum(case when col2='x' then 1 else 0 end) as count_col2,
       sum(case when col3='x' then 1 else 0 end) as count_col3
  from tab;

If you want to have sum of these count values, consider the above query as an inner and use the following :

 Select q.*,
        q.count_col1 + q.count_col2 + q.count_col3 whole_sum
   from     
     (
        Select sum(case when col1='x' then 1 else 0 end) as count_col1,
               sum(case when col2='x' then 1 else 0 end) as count_col2,
               sum(case when col3='x' then 1 else 0 end) as count_col3
          from tab  
      ) q

Rextester Demo

edited Jan 6, 2019 at 18:10

answered Jan 6, 2019 at 17:57

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ayesa Jannat Over a year ago

Thanks for your reply. Do I can sum of all the columns value as well?

Barbaros Özhan Over a year ago

@AyesaJannat Welcome, yes, sure depending on your data's syntax.

Ayesa Jannat Over a year ago

What to do for having the total of 3 columns count?

Ayesa Jannat Over a year ago

Wow!! Thanks a lot.

Parfait Over a year ago

Even simpler is sum the conditions without case: sum(col1='x') as count_col1,

|

Collectives™ on Stack Overflow

Multiple Count with Multiple column

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related