CODEWITHSUNDEEP
mysql
Jury A
Jury A

Reputation: 20052

What is wrong with this query?

I have a table with the following fields: id | domainname | domain_certificate_no | keyvalue

An example for the output of a select statement can be as:

'57092', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_1', '55525772666'
'57093', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_2', '22225554186'
'57094', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_3', '22444356259'
'97168', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_1', '55525772666'
'97169', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_2', '22225554186'
'97170', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_3', '22444356259’

I want to write a query to retrieve the number of repeated keyvalue, and which keyvalue that is associated with more than one domain_certificate_no with cert _1. So I wrote this:

select count(keyvalue), keyvalue from db.table group by (keyvalue) 
having count(keyvalue)>1 and domain_certificate_no like '%_1';

I get an error number 1054 saying unknown column domain_certificate_no. Can you tell me what is wrong? How can I write query to achieve my purpose ?

Upvotes: 1

Views: 76

Answers (3)

Tom
Tom

Reputation: 311

SELECT
    COUNT(keyvalue) AS keyvaluecount, keyvalue 
FROM 
    db.table 
HAVING
    keyvaluecount > 1 AND domain_certificate_no LIKE '%_1' 
GROUP BY 
    (keyvalue)

Upvotes: 0

Sebas
Sebas

Reputation: 21522

Just about the logic:

SELECT COUNT(domain_certificate_no), keyvalue
FROM db.table 
WHERE domain_certificate_no LIKE '%_1'
GROUP BY keyvalue
HAVING COUNT(keyvalue)>1;

If there's still your unknown column error, please show the result of DESC db.table;

Cheers.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

LIKE keyword should be in your WHERE clause

   SELECT domain_certificate_no, COUNT(keyvalue) 
     FROM db.table 
    WHERE domain_certificate_no LIKE '%_1'
 GROUP BY domain_certificate_no
   HAVING COUNT(keyvalue) > 1;

Upvotes: 1

Related Questions