CODEWITHSUNDEEP
mysqlsql
Loco Motive
Loco Motive

Reputation: 69

What is missing from this code to help me export how many times a person wrote a review for specific items

I am trying to get the total review count per profile when specific ASINs (products) are called.

For example, if I have 6 ASINs, I want to know if the person has written a product review for any of these ASINs. I am able to do this now.

But I am unable to do the following. If the person has written a review for more than 1 of the 6 asins, I want to know the total count.

Here's the code I am using:

select * from `reviewinfo` where `reviewAuthorURL` IN
(
SELECT `reviewAuthorURL` FROM `reviewinfo` WHERE `asin` in ('xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx', 'xxx') group by `reviewAuthorURL` HAVING COUNT(`reviewAuthorURL`)>1
    ) and `asin` IN
    (
    'xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx', 'xxx'
    )

Upvotes: 0

Views: 42

Answers (1)

Eric
Eric

Reputation: 3257

Try just this code

SELECT `reviewAuthorURL`, COUNT(reviewAuthorURL) AS 'TheCount' 
FROM `reviewinfo` 
WHERE `asin` in ('xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx', 'xxx') 
GROUP BY `reviewAuthorURL` 
HAVING COUNT(`reviewAuthorURL`)>1

Try this edited query:

SELECT `asin`, COUNT(asin)
FROM `reviewinfo`
WHERE `reviewAuthorURL` IN
(
    SELECT `reviewAuthorURL`
    FROM `reviewinfo` 
    WHERE `asin` in ('xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx','xxx', 'xxx','xxx', 'xxx') 
    GROUP BY `reviewAuthorURL` 
    HAVING COUNT(`reviewAuthorURL`)>1
) 
GROUP BY `asin`

Upvotes: 2

Related Questions