CODEWITHSUNDEEP
sqlpostgresql
hovnatan
hovnatan

Reputation: 1385

joing two tables with count values

I have the following two tables:

items:
id    pr1  pr2
--------------
1     11   22
...

and a table of comments associated with the items:

comments:
item_id text
------------
1 "cool"
1 "very good"
...

Now I want to get a table with columns item_id pr1 pr2 count(comments). What is the best way to get it? Thanks!

Upvotes: 0

Views: 68

Answers (2)

xlecoustillier
xlecoustillier

Reputation: 16351

Just do it in one single query:

SELECT items.id, 
       items.pr1, 
       items.pr2, 
       COUNT(*) AS comment_count
FROM   items 
       INNER JOIN comments 
               ON items.id = comments.item_id 
GROUP  BY items.id, 
          items.pr1, 
          items.pr2

EDIT:

If all the ungrouped columns are functionnally dependent of the grouped one, it appears that you can group by only this column (as Yash did):

SELECT items.id, 
       items.pr1, 
       items.pr2, 
       COUNT(*) AS comment_count
FROM   items 
       INNER JOIN comments 
               ON items.id = comments.item_id 
GROUP  BY items.id

See:

EDIT 2:

About adding a second table (let's take tags):

You can't just go on COUNTing on *. Try that:

SELECT items.id, 
       items.pr1, 
       items.pr2, 
       COUNT(DISTINCT comments.*) AS comment_count,
       COUNT(DISTINCT tags.*) AS tags_count
FROM   items 
       INNER JOIN comments 
               ON items.id = comments.item_id 
       INNER JOIN tags 
               ON items.id = tags.item_id 
GROUP  BY items.id, 
       items.pr1, 
       items.pr2

By using DISTINCT, you'll COUNT each row in comments only once, and the same with tags.

Upvotes: 2

Yash
Yash

Reputation: 1436

Try this:

select items.id, items.pr1, items.pr2, count(*) as comment from items,comments where items.id = comments.item_id group by items.id

Upvotes: 3

Related Questions