CODEWITHSUNDEEP
mysqlsql
Rammehar Sharma
Rammehar Sharma

Reputation: 57

Nested Count with having Clause mysql?

I want to count images selected from different categories my query below return error

#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 '( select count(i1.id) as cnt FROM image_gallery i1 LEFT OUTER JOIN image_g' at line 2

Where I am wrong ? 

SELECT count(*)
(select count(*)
FROM image_gallery i1
LEFT OUTER JOIN image_gallery i2
  ON (i1.category_id = i2.category_id AND i1.id < i2.id)
GROUP BY i1.id
HAVING COUNT(*) < 10 
) as total_pics

Upvotes: 0

Views: 498

Answers (1)

juergen d
juergen d

Reputation: 204784

You missed the FROM keyword

SELECT sum(cnt)
FROM
(
  select count(i1.id) as cnt
  FROM image_gallery i1
  LEFT OUTER JOIN image_gallery i2 ON i1.category_id = i2.category_id 
                                  AND i1.id < i2.id
  GROUP BY i1.id
  HAVING COUNT(*) < 10 
) as total_pics

Upvotes: 1

Related Questions