How to debug "You have an error in your SQL syntax" in MySQL?

Question

SELECT loyalty_card.card_ID, Customer.first_name,customer.last_name, loyalty_card.points
FROM loyalty_card
INNER JOIN customer
ON loyalty_card.Customer_ID=Customer.Customer_ID;
having count(loyalty_card.points) > 100

I keep trying to complete the query, but I am getting error messages.

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax 
to use near 'having count(loyalty_card.points) > 100' at line 1

Can anyone help? I am using phpMyAdmin and MySQL.

You have a semicolon before the word having, so it is being treated a two separate SQL statements: SELECT loyalty_card.card_ID ... Customer.Customer_ID; and having count(loyalty_card.points) > 100, the latter of which is not being terminated by a semicolon. — GoBusto
– GoBusto, Commented Mar 9, 2015 at 17:55
You need to have a GROUP BY clause in order to use COUNT() and HAVING. — Tab Alleman
– Tab Alleman, Commented Mar 9, 2015 at 17:55
Heh no @Strawberry, just doing a bit of fluff deletion. Thanks in advance. Please help! — halfer
– halfer, Commented Aug 9, 2015 at 9:10

Tushar · Accepted Answer · 2015-03-09 18:19:03Z

1

In order to use 'Having clause' with an aggregate function you need to have 'group by' clause in your query.

The syntax is as follows:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

So; your query becomes:

Select l1.card_ID, c1.first_name, c1.last_name, l1.points 
FROM loyalty_card l1
INNER JOIN customer c1 
ON l1.Customer_ID = c1.Customer_ID
group by l1.card_ID
having count(l1.points) > 100

answered Mar 9, 2015 at 18:19

Tushar

3,6759 gold badges31 silver badges51 bronze badges

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

Comments

Md. Nasir Uddin Bhuiyan · Accepted Answer · 2015-03-09 18:01:30Z

0

Try this:

SELECT loyalty_card.card_ID, Customer.first_name,customer.last_name, loyalty_card.points 
FROM loyalty_card INNER JOIN customer ON loyalty_card.Customer_ID = Customer.Customer_ID
group by loyalty_card.card_ID
having count(loyalty_card.points) > 100

answered Mar 9, 2015 at 18:01

Md. Nasir Uddin Bhuiyan

203 bronze badges

Collectives™ on Stack Overflow

How to debug "You have an error in your SQL syntax" in MySQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related