SQL Error (unknown column in having clause).

Question

[SQL]SELECT vlog.lead_id, vlog.phone_number FROM vicidial_log as vlog GROUP BY vlog.lead_id HAVING( CASE WHEN vlog.status = 'NA' THEN 1 WHEN vlog.status = 'ADC' THEN 1 END ) = COUNT(*) [Err] 1054 - Unknown column 'vlog.status' in 'having clause'

The query above is not working. I am trying to achieve this: https://www.db-fiddle.com/f/ej4fM8GptBk9FvGJC8AkUH/0 in sense.

 [SQL]SELECT vlog.lead_id, vlog.phone_number, vlog.status FROM vicidial_log as vlog GROUP BY vlog.lead_id HAVING( CASE WHEN vlog.status = 'NA' THEN 1 WHEN vlog.status = 'ADC' THEN 1 END ) = COUNT(*)

THIS WORKS BUT IT DOESN'T GIVE ME THE RESULTS I WANT

status = can be anything lead_id = not unique phone_number = not unique

I am trying to find phone numbers WHERE atleast the status is either

ADC NA OR BOTH ADC AND NA

Any other combination should not return the phone number.

Data in DB fiddle doesn't correspond to what you are talking about... — Michał Turczyn
– Michał Turczyn, Commented Jun 21, 2018 at 18:26
You left out the count aggregate from the having clause. mysql allows this syntax in your 2nd example, but could return misleading results. It should be having count(case... . — sgeddes
– sgeddes, Commented Jun 21, 2018 at 18:37

Michał Turczyn · Accepted Answer · 2018-06-21 18:28:37Z

2

It's shot in the dark, but maybe it will work for you. Change your HAVING clause to:

HAVING SUM(CASE WHEN vlog.status in ('NA','ADC') THEN 1 ELSE 0 END) = COUNT(*)

answered Jun 21, 2018 at 18:28

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

3 Comments

M Khalid Junaid Over a year ago

According to his description this is correct answer, if lead_id has both ADC and NA and no other status then count(*) would be 2 and sum(..) will also return 2 same goes for if lead_id has only one status which either ADC or NA the count and sum will be 1 which again meets the criteria

Vish Over a year ago

How can I implement the query so that atleast 10 records exist

Michał Turczyn Over a year ago

It depends on data you have :)

Aaron Dietz · Accepted Answer · 2018-06-21 18:26:59Z

It seems like this would work:

SELECT DISTINCT lead_id, phone_number FROM vicidial_log vlog WHERE status IN ('NA', 'ADC') AND NOT EXISTS (SELECT * FROM vicidial_log vlog2 WHERE status NOT IN ('NA','ADC') AND vlog.lead_id = vlog2.lead_id AND vlog.phone_number = vlog2.phone_number)

It's not 100% clear whether you need the comparison to be on both lead_id and phone_number though.

Collectives™ on Stack Overflow

SQL Error (unknown column in having clause).

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related