Counting rows

[chribben]

Hello!
I have two tables; one containing product names and one with reviews of the products. What I'd like to do is present a table with the product names and number of reviews. The thing is that there can be more than one entry of a certain product in the products table. When I write something like:
SELECT products.name, COUNT(reviews.review)
FROM products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name
I will get a table like:

ProductA 3
ProductA 3
ProductB 1
ProductC 0

In this case ProductA appears twice in the products table and has 3 reviews in the reviews table. But I just want it to appear once in the joined table, how to do? Any tip highly appreciated.

/Chris

[csecharith]

Try LEFT OUTER JOIN instead of simple join.

[ck9663]

Hello!
I have two tables; one containing product names and one with reviews of the products. What I'd like to do is present a table with the product names and number of reviews. The thing is that there can be more than one entry of a certain product in the products table. When I write something like:
SELECT products.name, COUNT(reviews.review)
FROM products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name
I will get a table like:

ProductA 3
ProductA 3
ProductB 1
ProductC 0

In this case ProductA appears twice in the products table and has 3 reviews in the reviews table. But I just want it to appear once in the joined table, how to do? Any tip highly appreciated.

/Chris

Is there a product table that contains some sort of product id that is unique ?

-- CK

[balame2004]

Hi,

Try this:

SELECT distinct products.name, COUNT(reviews.review)
FROM products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name


Balaji U

[chribben]

Is there a product table that contains some sort of product id that is unique ?

-- CK

Yes, each entry has a guid like so:
GUID Name
==== ====
0x01 ProductA
0x02 ProductB
0x03 ProductA
0x04 ProductC

/Chris

[chribben]

Hi,

Try this:

SELECT distinct products.name, COUNT(reviews.review)
FROM products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name


Balaji U

Doesn't matter if I use DISTINCT since I'm grouping by product name.
If I have product table like this:
Name
=======
ProductA
ProductA
ProductA
ProductB
ProductC

...and a review table like this:
Prod_name Review
======== =====
ProductA bla bla
ProductA bla bla
ProductB bla bla

The joined table will be:
Product No. of reviews
======== ==========
ProductA 6
ProductB 1
ProductC 0

/Chris

[chribben]

Try LEFT OUTER JOIN instead of simple join.

Doesn't work either, see post above.

/Chris

[ck9663]

If PRODUCTNAME is also in reviews, why not just query the REVIEWS table?

SELECT reviews.name, COUNT(*) as cnt
FROM reviews 
GROUP BY reviews.name

if you just want to make sure if it's existing in PRODUCTS table just do a WHERE IN condition...

-- CK

[chribben]

If PRODUCTNAME is also in reviews, why not just query the REVIEWS table?

SELECT reviews.name, COUNT(*) as cnt
FROM reviews 
GROUP BY reviews.name

if you just want to make sure if it's existing in PRODUCTS table just do a WHERE IN condition...

-- CK

Because then the products that have zero reviews won't be listed and I need to include them as well.

/Chris

[ck9663]

You should consider using GUID as key. For now am following your requirement, that you're using the name as key. Try:

SELECT products.name, COUNT(reviews.review)
FROM (select distinct name from products) products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name

-- CK

[chribben]

You should consider using GUID as key. For now am following your requirement, that you're using the name as key. Try:

SELECT products.name, COUNT(reviews.review)
FROM (select distinct name from products) products
JOIN reviews ON products.name = reviews.productname
GROUP BY products.name

-- CK

Using FULL JOIN, worked like a charm! Thanks a lot!

/Chris