mysql — How to get the count of each distinct value in a column?

Question:
This question already has answers here : https://sqlerrors.com/howto/392/mysql---How-to-get-the-count-of-each-distinct-value-in-a-column?#mysql---How-to-get-the-count-of-each-distinct-value-in-a-column?
Count the occurrences of DISTINCT values (3 answers)
Closed last year at : https://sqlerrors.com/.
I have an SQL table called "posts" that looks like this:

id | category

1 | 3
2 | 1
3 | 4
4 | 2
5 | 1
6 | 1
7 | 2
Each category number corresponds to a category. How would I go about counting the number of times each category appears on a post all in one SQL query?

As an example, such a query might return a symbolic array such as this: (1:3, 2:2, 3:1, 4:1)

My current method is to use queries for each possible category, such as: SELECT COUNT(*) AS num FROM posts WHERE category=#, and then combine the return values into a final array. However, I'm looking for a solution that uses only one query.

Solution 1:

SELECT
category,
COUNT(*) AS num
FROM
posts
GROUP BY
category
mysql - How to count the number of instances of each foreign-key ID in a table?
Question:
Here's my simple SQL question...

I have two tables:

Books

---

| book_id | author | genre | price | publication_date |

Orders

---

| order_id | customer_id | book_id |

I'd like to create a query that returns:

---

| book_id | author | genre | price | publication_date | number_of_orders |

In other words, return every column for ALL rows in the Books table, along with a calculated column named 'number_of_orders' that counts the number of times each book appears in the Orders table. (If a book does not occur in the orders table, the book should be listed in the result set, but "number_of_orders" should be zero.

So far, I've come up with this:

SELECT
books.book_id,
books.author,
books.genre,
books.price,
books.publication_date,
count(*) as number_of_orders
from books
left join orders
on (books.book_id = orders.book_id)
group by
books.book_id,
books.author,
books.genre,
books.price,
books.publication_date
That's almost right, but not quite, because "number_of_orders" will be 1 even if a book is never listed in the Orders table. Moreover, given my lack of knowledge of SQL, I'm sure this query is very inefficient.

What's the right way to write this query? (For what it's worth, this needs to work on MySQL, so I can't use any other vendor-specific features).

Thanks in advance!

Solution 1:

Your query is almost right and it's the right way to do that (and the most efficient)

SELECT books., count(orders.book_id) as number_of_orders

from books
left join orders
on (books.book_id = orders.book_id)
group by
books.book_id
COUNT() could include NULL values in the count because it counts all the rows, while COUNT(orders.book_id) does not because it ignores NULL values in the given field.

Solution 2:

SELECT b.book_id,
b.author,
b.genre,
b.price,
b.publication_date,
coalesce(oc.Count, 0) as number_of_orders
from books b
left join (
select book_id, count(*) as Count
from Order
group by book_id
) oc on (b.book_id = oc.book_id)
Solution 3:

Change count(*) to count(orders.book_id)