计算特定ID的行数

Question

In first table album has id and second table album_details has sub_id which relates from album table id

I need to display count for separate id value.

SELECT DISTINCT B.SUB_ID, A . * , B.CONTENT_VALUE AS detail, 
(SELECT COUNT( ID ) 
FROM album_details WHERE A.ID = B.SUB_ID ) AS count
FROM album AS A, album_details AS B
WHERE A.WEBSITE_ID =  '571710720'
AND A.ID = B.SUB_ID
GROUP BY B.SUB_ID
LIMIT 0 , 30

Now count column shows 40 for all rows but need to display 'count' 6 for 'id=4', 'count' 3 for 'id=2'

Answer 1

GROUP BY is your weapon of choice.

SELECT 
    a.ID,
    a.CONTENT_VALUE,
    COUNT(ad.ID)
FROM albums AS a
LEFT JOIN album_details AS ad ON a.ID = ad.SUB_ID
GROUP BY a.ID

Feel free to add your WHERE before the GROUP BY.