Query that checks for a value A to appear at least B number of times in C columns

Question

I'm trying to select rows from a table where some value a is within c columns at least b times.

Here is a simplified example of the data I'm working with. I'm trying to find rows that have an h in at least 2 of the greetings.

greeting1	greeting2	greeting3	farewell1	farewell2
hi	hello	hey there	goodbye	peace
hi	sup	yo	Au revoir	see ya
yo	hola	ayyyy	hang tight	reverse-hi
hola	sup	hello	see ya	peace
hello	yo	hola	hang tight	ciao
ayyy	yo	hola	hang tight	ciao

And below are the rows I'd like to grab:

greeting1	greeting2	greeting3	farewell1	farewell2
hi	hello	hey there	goodbye	peace
hola	sup	hello	see ya	peace
hello	yo	hola	hang tight	ciao

(I italicized and bolded for easier ability to see)

I'm just learning SQL, so this might be basic. I was having some difficulties. I know getting the rows and checking all greeting1 would look something like:

SELECT * FROM GreetingsAndFarewell WHERE greetings1 LIKE '%h%' AND greetings2 LIKE '%h%' AND greetings3 LIKE '%h%'

But what if I only care if at least 2 greetings have an h in them?

Here are some of the similar questions I've seen, but I don't think either of them really address this:

5760335 31577623

If you add your sample data as DDL+DML you make it much easier for people to assist. — Dale K
– Dale K, Commented Oct 29, 2021 at 23:16
And SQL Server <> Google Big Query - please only tag the relevant product. — Dale K
– Dale K, Commented Oct 29, 2021 at 23:17

Tim Biegeleisen · Accepted Answer · 2021-10-29 23:20:15Z

You could use try summing CASE expressions which check for the letter h in each of the 3 greetings:

SELECT * FROM GreetingsAndFarewell WHERE CASE WHEN greetings1 LIKE '%h%' THEN 1 ELSE 0 END + CASE WHEN greetings2 LIKE '%h%' THEN 1 ELSE 0 END + CASE WHEN greetings3 LIKE '%h%' THEN 1 ELSE 0 END >= 2;

On BigQuery you might be able to directly sum boolean expressions and simplify to:

SELECT * FROM GreetingsAndFarewell WHERE greetings1 LIKE '%h%' + greetings2 LIKE '%h%' + greetings3 LIKE '%h%' >= 2;

Thanks, I'm not having luck on the second portion of your answer, but the first works perfectly.

Mikhail Berlyant · Accepted Answer · 2021-10-29 23:31:15Z

consider below approach (BigQuery)

select * from GreetingsAndFarewell where ( select count(1) from unnest([greeting1, greeting2, greeting3]) greeting where greeting like '%h%' ) >= 2

if applied to sample data in your question - output is

Formatted text is the preferred method of displaying data. It works better for everyone.

Collectives™ on Stack Overflow

Query that checks for a value A to appear at least B number of times in C columns

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related