count string occurrences across multiple column sql [duplicate]

Question

How can I count total no. of $ signs present in a table? Simple select query will not work in this case. How should I edit my select query to count string occurrences across multiple columns in a table.

c1 | c2 | c3 | c4 | c5|
$  |    |    | $  |   |  
   | $  |  $ | $  |   |   
   |    |    |    |   |
$  |    |    |    |   |

($ signs are present randomly in the table in any column) I tried to get the count through regex but it always return no. of rows in which $ is present, not the number of $.

No regular expressions are needed. Is this about PostgreSQL or MySQL? — Laurenz Albe
– Laurenz Albe, Commented Jul 3, 2018 at 10:58
You are trying to pivot the data. MySQL does not have a pivot function. You will have to use case statement with count. — skelwa
– skelwa, Commented Jul 3, 2018 at 11:02

M Khalid Junaid · Accepted Answer · 2018-07-03 11:00:42Z

3

You could use case inside count to count occurrence of $ in your columns

select 
  count(case when c1 = '$' then 1 else null end) 
+ count(case when c2 = '$' then 1 else null end)
+ count(case when c3 = '$' then 1 else null end) 
+ count(case when c4 = '$' then 1 else null end) 
+ count(case when c5 = '$' then 1 else null end) cnt
from your_table

Demo Mysql

Demo Postgre

answered Jul 3, 2018 at 11:00

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

1 Comment

daemonThread Over a year ago

M khalid Junaid, it worked, thanks! :)

Collectives™ on Stack Overflow

count string occurrences across multiple column sql [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related