CODEWITHSUNDEEP
mysql
D.Wells
D.Wells

Reputation: 125

How to return count values of 0 when finding the number of rows that satisfy a condition

$query = mysqli_query($conn, "SELECT BLOGGER_URL, COUNT(*) as NUM FROM Blog_Posts    GROUP BY URL ORDER BY NUM DESC");

I'm using this query to return rows of data ordered based on the number of posts that a blogger has made. Each post has a blogger url ascribed to it, so I'm counting the number of blogger urls to determine the count. I'm trying to order some rows based on the the number of posts made by a a blogger but right now the query only returns values that have a count of 1 or more. So Bloggers with no posts are not returned.

How can I edit the query to return ALL the bloggers in order?

Thanks, I'm pretty new to databases in general.

EDIT:

I have two tables relevant here: a bloggers table with a primary key blogger url and a blog posts table that uses the blogger url as a FK. The blog posts refer to a given blogger in the bloggers table

Upvotes: 0

Views: 80

Answers (2)

A2AD2D
A2AD2D

Reputation: 36

You should join your query to the users table so that you get a count of all users not just those with posts. I don't know your table structure, but you can do it in the following basic way.

SELECT Blog_Users.userid, COUNT(Blog_Posts.*) as NUM 
FROM Blog_Users
LEFT OUTER JOIN Blog_Posts ON Blog_Posts.BLOGGER_USERID= Blog_Users.userid
GROUP BY Blog_Users.userid ORDER BY NUM DESC

Upvotes: 1

Amazigh.Ca
Amazigh.Ca

Reputation: 3513

It's normal that you have a count of 1 or more, cause the Bloggers that have not post does not exist in Blog_Post table, so if you want them returned, just join your Bloggers Table in the query with a left join.

Hope this heleped you

Upvotes: 0

Related Questions