472,143 Members | 1,525 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,143 software developers and data experts.

Counting rows part II

I have the following query:
1. SELECT products.name, COUNT(reviews.review)
2. FROM (select distinct name from products) products
3. FULL JOIN reviews ON products.name = reviews.productname
4. GROUP BY products.name
which lists product names and the number of reviews for each product. I need to include one more column from the products table in the table resulting from the above query. This column (called altname) can have different values for a certain product (each product can appear multiple times in the table). It doesn't matter which of the altname values for a product that is presented in the resulting table as long as someone is. Is this possible? See example below.
The products table:
products.name products.altname
ProdA prd_a
ProdA proda
ProdB prb
ProdB prodb

...and the review table:
reviews.prodname review
ProdA bla bla....
ProdA bla bla....
ProdB bla bla....

should result in:
name altname reviews_count
ProdA prd_a 2
ProdB prb 1

Apr 14 '08 #1
3 1092
8,127 Expert 4TB
Is it such that for the same products.name you have different products.altname ?
Apr 14 '08 #2
Is it such that for the same products.name you have different products.altname ?
Yes, that is correct.

Apr 14 '08 #3
2,878 Expert 2GB
You might want to reconsider re-designing your tables and their relationship.

-- CK
Apr 14 '08 #4

Post your reply

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

Similar topics

16 posts views Thread by walexand | last post: by
1 post views Thread by Tony Johansson | last post: by
3 posts views Thread by Megan | last post: by
18 posts views Thread by ChadDiesel | last post: by
3 posts views Thread by nickels | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.