Query JOIN And Count ON Postgresql

Question

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.

SELECT free_value, count(*) from a_table LEFT JOIN b_table ON a_table.id = b_table.a_table_id group by free_value — are
– are, Commented Aug 2, 2016 at 22:29

Uueerdo · Accepted Answer · 2016-08-02 22:36:12Z

1

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.

edited Aug 2, 2016 at 22:36

answered Aug 2, 2016 at 22:29

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alim Özdemir · Accepted Answer · 2016-08-02 22:29:37Z

1

try

SELECT free_value, count(free_value)
from a_table LEFT JOIN b_table ON a_table.id = b_table.a_table_id 
group by free_value

answered Aug 2, 2016 at 22:29

Alim Özdemir

2,6431 gold badge27 silver badges36 bronze badges

2 Comments

Uueerdo Over a year ago

counting free_value will result in 1's for values with no references.

Thailand Love U Over a year ago

I change count(free_value) to count(b_table.id) and it work right thank for @Uueerdo too. Thank you for help.

Vishal Gupta · Accepted Answer · 2016-08-02 22:58:06Z

0

SELECT free_value, count( b_table.id ) count
FROM a_table
LEFT JOIN b_table ON a_table.id = b_table.a_table_id
GROUP BY free_value
ORDER BY count DESC

try this query.

answered Aug 2, 2016 at 22:58

Vishal Gupta

1246 bronze badges

Collectives™ on Stack Overflow

Query JOIN And Count ON Postgresql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related