CODEWITHSUNDEEP
phpmysql
user3386779
user3386779

Reputation: 7175

Count number of rows for specific ID

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

enter image description here

enter image description here

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'

Upvotes: 2

Views: 2180

Answers (4)

Bishoy Bisahi
Bishoy Bisahi

Reputation: 377

SELECT count(SUB_ID),SUB_ID from album_details group by SUB_ID

Upvotes: 2

Gopalakrishnan
Gopalakrishnan

Reputation: 957

select count(sub_id) as count1 from album_details where sub_id in(select id from album) WHERE album.WEBSITE_ID = '571710720' AND album.ID = album_details.SUB_ID

Upvotes: 1

Sergey Vidusov
Sergey Vidusov

Reputation: 1342

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.

Upvotes: 1

PHPExpert
PHPExpert

Reputation: 943

Lets say first table is A and second table is B then query will be like this

select a.ID, count(b.SUB_ID) AS total
FROM A LEFT JOIN B ON A.ID = B.SUB_ID
Group by B.SUB_ID.

It might help you. If not then ask please.

Upvotes: 1

Related Questions