Help with SQL query

Question

Hello all,

Lets say I have one table called "REVIEWS"

This table has Reviews that customers have written for various products.

I would like to be able to get a "count" for all the reviews that each customer has written, so i write:

SELECT count(*) AS counter
FROM reviews 
WHERE customers_id = 12345

Now, my problem is that I wish to have a count like above BUT only for customers who have written a SPECIFIC product review

For instance,

SELECT customers_review
FROM reviews
WHERE
products_id = '170'

In summary, I wish to be able to get the customers TOTAL COUNT for every review they have written, but ONLY for customers who have written a review for a specific product.

Thank you very much for your help.

Answer 1

select customers_id, count(*)
from reviews
where customers_id in 
(select customers_id from reviews where products_id = '170')
group by customers_id;

Answer 2

Just add to the end of your first query

WHERE EXISTS (
SELECT 1 FROM reviews AS r 
WHERE r.customers_id = reviews.customers_id 
    AND product_id = '170')
GROUP BY reviews.customers_id

Answer 3

I might be able to help you out, but it would be helpful if I knew the structure of the tables you are trying to query.

Answer 4

SELECT customers_id, COUNT(*) AS counter
FROM reviews 
WHERE customers_id IN(
SELECT customers_id
FROM reviews
WHERE
products_id = '170'
)
GROUP BY customers_id

This will pull any customer who wrote about product X, and then count up total number of reviews they posted.

Answer 5

Select customer_ID, Count(*)
FROM reviews
WHERE customer_ID in ( Select Customer_ID from reviews where products_id = '170')
Group By customer_ID

This should give you a list of all CustomerID's along with the Count of all their reviews, but it will limit it only to the customers who have left a review for product 170.

Answer 6

Not disagreeing with the approaches in this thread. But I think windowing/analytic SQL offer another way of looking at this problem. You can get the counts:

• Count of Reviews by Customer on this detail row
• Count of Reviews By product on this detail row
• Count of Reviews by this customer on this product on this detail row
• Any details (the text of the review, the review ID etc...)

SQL Statement

SELECT
 revid, 
 CUSTID, 
 PRODID,
 COUNT (*) OVER (PARTITION BY custid) ByCust,
 COUNT (*) OVER (PARTITION BY prodid) ByProd,
 COUNT (*) OVER (PARTITION BY prodid, custid) ByCustProd

FROM customer_reviews
ORDER BY custid, prodid

--OUTPUT--

1520  106  1900 16  604 3
4650  106  1900 16  604 3
2730  106  1900 16  604 3
4640  106  3900 16  254 1
6287  110  1900 28  604 2
5849  110  1900 28  604 2
5965  110  3900 28  254 2
6117  110  3900 28  254 2