CODEWITHSUNDEEP
mysqljoincount
sgtBear
sgtBear

Reputation: 61

COUNT not working in Query with JOIN (SQL)

I tried to add a COUNT in my MySQL query, because i need the amount of all rows.

Orginal: SELECT a.*, b.device_name, FROM ( SELECT * FROM qslist ORDER BY ID DESC LIMIT 500) a JOIN device b ON a.device = b.ID

NEW: SELECT a.*, b.device_name, a.COUNT(ID) AS out FROM ( SELECT * FROM qslist ORDER BY ID DESC LIMIT 500) a JOIN device b ON a.device = b.ID

Sadly, the new Query, does not get executed, the following error-message is shown :

#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 'out FROM ( SELECT * FROM qslist ORDER BY ID DESC LIMIT 500) a JOIN device b ON a' at line 1

What is the problem here?

Upvotes: 0

Views: 387

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

out is a reserved word in MySQL. You can escape it or name it something else:

SELECT a.*, b.device_name, COUNT(a.ID) AS cnt
FROM ( SELECT * FROM qslist ORDER BY ID DESC LIMIT 500) a JOIN
     device b
     ON a.device = b.ID;

In addition, the a. goes next to id, not count().

That fixes the syntax error.

This query is unlikely to be doing what you want, because it will return only one row. The count() makes this an aggregation query with no group by. However, your question is about the syntax error, not the correct query for your unstated purposes. (If you want to fix the query, I would suggest asking another question with sample data and desired results.)

Upvotes: 2

Azar
Azar

Reputation: 1

Your Count will provide you a single row. but the a.* and b.device_name might have muliple rows. to find the count you need to have a separate query that would be better.

Upvotes: 0

Related Questions