Get reply numbers in mysql

Question

I am working on a product review page where it will display several submitted reviews as well as the number of comments to each of them.

I thought I could use

SELECT title AS review_title,COUNT(id_group) AS Approved_reply_number 
WHERE approved <> '0' 
GROUP BY id_group`

but read somewhere that it isn't possible to copy the id values into another row on the insert process. So if someone submits a review, the id_group field for the reviews has to be left empty.

Here is the table example:

CREATE TABLE product_review
    (`ID` int, `title` varchar(21), `id_group` int,`approved` int)
;

INSERT INTO product_review
    (`ID`, `title`, `id_group`,`approved`)
VALUES
    (1, 'AAA', Null,1),
    (2, 'BBB', 1,1),
    (3, 'CCC', Null,1),
    (4, 'DDD', 3,0),
    (5, 'EEE', 1,1),
    (6, 'FFF', Null,1),
    (7, 'GGG', 6,1),
    (8, 'HHH',1,1),
    (9, 'III', 6,1)
;

Those that are Null in id_group are the submitted reviews. The rest are replies and they contain the id of their corresponding reviews. I was wondering how can I get an output like this:

review_title   approved_reply_number
  AAA             3           
  CCC             0
  FFF             2

Hi, maybe have a look at this question : http://stackoverflow.com/questions/3837990/last-insert-id-mysql — Steven, Jul 24 '14 at 21:00

score 2 · Accepted Answer · answered Jul 24 '14 at 21:05

You can use a self join and count query with group by and also a where clause to filter out reviews only

select t.title review_title ,count(*) approved_reply_number
from product_review t
left join product_review t1 on(t.id = t1.id_group)
where t.id_group is null
group by t.id

Demo

Get reply numbers in mysql

1 Answers1