By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Counting rows

P: 14
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
Apr 5 '08 #1
Share this Question
Share on Google+
10 Replies


P: 3
Try LEFT OUTER JOIN instead of simple join.
Apr 5 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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
Apr 7 '08 #3

100+
P: 142
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
Apr 7 '08 #4

P: 14
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
Apr 7 '08 #5

P: 14
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
Apr 7 '08 #6

P: 14
Try LEFT OUTER JOIN instead of simple join.
Doesn't work either, see post above.

/Chris
Apr 7 '08 #7

ck9663
Expert 2.5K+
P: 2,878
If PRODUCTNAME is also in reviews, why not just query the REVIEWS table?

Expand|Select|Wrap|Line Numbers
  1. SELECT reviews.name, COUNT(*) as cnt
  2. FROM reviews 
  3. 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
Apr 7 '08 #8

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

Expand|Select|Wrap|Line Numbers
  1. SELECT reviews.name, COUNT(*) as cnt
  2. FROM reviews 
  3. 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
Apr 8 '08 #9

ck9663
Expert 2.5K+
P: 2,878
You should consider using GUID as key. For now am following your requirement, that you're using the name as key. Try:


Expand|Select|Wrap|Line Numbers
  1. SELECT products.name, COUNT(reviews.review)
  2. FROM (select distinct name from products) products
  3. JOIN reviews ON products.name = reviews.productname
  4. GROUP BY products.name
-- CK
Apr 8 '08 #10

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


Expand|Select|Wrap|Line Numbers
  1. SELECT products.name, COUNT(reviews.review)
  2. FROM (select distinct name from products) products
  3. JOIN reviews ON products.name = reviews.productname
  4. GROUP BY products.name
-- CK
Using FULL JOIN, worked like a charm! Thanks a lot!

/Chris
Apr 8 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.