Thailand Love U Thailand Love U - 4 months ago 10
MySQL Question

Query JOIN And Count ON Postgresql

I use Postgresql. I have a & b table like this

a_table
+--------+---------------+
| id | free_value |
+--------+---------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
| 4 | 3 |
| 5 | 2 |
| 6 | 8 |
| 7 | 4 |
+--------+---------------+

b_table
+--------+---------------+
| id | a_table_id |
+--------+---------------+
| 1 | 2 |
| 2 | 2 |
| 3 | 6 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
+--------+---------------+


Can I write query for count free_value on b_table and desc to count like this ?

count_free_value_table
+----------------+-----------+
| free_value | count |
+----------------+-----------+
| 2 | 3 |
| 1 | 2 |
| 8 | 1 |
| 3 | 0 |
| 4 | 0 |
+----------------+-----------+


I try with
SELECT free_value, count(free_value) from a_table LEFT JOIN b_table ON a_table.id = b_table.a_table_id
But It is not work.

Thank you for any help. I am be silly for this.

Answer

Try COUNT(b_table.id) instead; COUNT counts all non-null values encountered, which is why (I am guessing) you were getting 1 for the unmatched free_values; because the unmatched values still have their own values in their rows.

Edit: Also, are's comment and Alim's answer are also correct in that you need to group by free_value. Otherwise, you'll get total rows of the JOIN, and an effectively randomly chosen free_value.