Subquery in select statement returning SQL error

Question

I get an SQL error for this query

Error Number: 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 '*)
FROM `cast_gallery` q
JOIN `all_media` r on q.all_media_id=r.id
WHERE q.pa' at line 1

SELECT `cast_name`, `cast_slug`, (SELECT count(q.*)
 FROM `cast_gallery` q
 JOIN `all_media` r on q.all_media_id=r.id
 WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, `view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

How to solve?

Madhur Bhaiya · Accepted Answer · 2018-10-08 12:21:14Z

1

To count total number of rows, we use COUNT(*), not Count(q.*). Also, there is no need of parentheses around casts in the From clause.

SELECT `cast_name`, 
       `cast_slug`, 
       (SELECT count(*)
        FROM `cast_gallery` q
        JOIN `all_media` r on q.all_media_id=r.id
        WHERE q.parent = `casts`.`id` AND 
              r.approved=1) as image_gallery, 
      `active`, 
      `view`, 
      `checked`, 
      `sex`, 
      `id`
FROM `casts`
ORDER BY `cast_name` asc
LIMIT 10

Additional Info:

COUNT(...) is used to count the number of non-NULL values; however COUNT(*) will count the total number of rows retrieved, whether or not they contain NULL values.

edited Oct 8, 2018 at 12:21

answered Oct 8, 2018 at 12:04

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

Comments

Jignesh Joisar · Accepted Answer · 2018-10-08 12:06:47Z

1

try to just change count(q.*) to count(*)

answered Oct 8, 2018 at 12:06

Jignesh Joisar

15.4k6 gold badges69 silver badges61 bronze badges

Comments

curious_dev · Accepted Answer · 2018-10-11 04:46:53Z

0

The Syntax for count is incorrect. If we want to find out the number of rows in subquery we can use count(*). Please refer to http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_func_count.asp.html for more clarifiction.

SELECT `cast_name`, `cast_slug`, (SELECT count(*)
FROM `cast_gallery` q
JOIN `all_media` r on q.all_media_id=r.id
WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, 
`view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

should work

edited Oct 11, 2018 at 4:46

answered Oct 8, 2018 at 12:18

curious_dev

364 bronze badges

Comments

Mahesh Hegde · Accepted Answer · 2018-10-11 05:01:00Z

0

you can try instead of count(q.*), you can use q.all_media_id

SELECT `cast_name`, `cast_slug`, (SELECT count(q.all_media_id)
 FROM `cast_gallery` q
 JOIN `all_media` r on q.all_media_id=r.id
 WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, `view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

answered Oct 11, 2018 at 5:01

Mahesh Hegde

1,23911 silver badges12 bronze badges

Collectives™ on Stack Overflow

Subquery in select statement returning SQL error

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related