samedi 23 avril 2016

How to use aggregation COUNT without counting the duplicated value in diferent rows in SQLITE?

I have a table named Review.It contains reviews given by the customers on the cleaning companies that they have been experienced before.Same ReviewerID can give many reviews on the same company(anytime).1 ReviewerID == 1 person.I decided to make every review to have a ReviewID as each review will has a different review_statement.So ReviewID is the PK and companyID is the FK.

reviewID | reviewerID | companyID | Statement
  001    |   James216 |  clean417 | "different statement"
  002    |   James216 |  clean417 | "different statement"
  003    |   Lee_216  |  clean417 | "different statement"

So I want to obtain the number of companies reviewed by each ReviewerID in the database.By refering the table, the desired output is James216 1 (1 because James216 only reviewed company clean417).I've tried using COUNT but it will count the non-null row,causing the output to be James216 2 .I've also tried using select distinct but the output wont be correct.I seriously cant figure this out.Thank you for helping!

Aucun commentaire:

Enregistrer un commentaire