MySQL Help Requested...

[SgtSpike]

I can't really figure out the best way to do this...

On my website, bitcoinfeedback, I have a few MySQL tables.  One of them is feedback, and one of them is users.

I'd like to easily be able to pull the percentage of positive vs negative vs neutral feedback a user has while generating a list of users, all with one query.

The only way I can think of to do this is to have three more fields in the users table that just keep an updated count of feedback any time someone leaves feedback on a person's profile.  It would run a query to count the three different types of feedback on a given users account, then update those three fields with the count numbers.  When pulling the users into a list, I could easily make whatever calculations I wanted to do with those three count numbers.

Is there a better way to do this?  Currently, feedback "type" is stored within a single varchar field within each feedback.  So the varchar field will either say "positive", "negative", or "neutral".  I can change that to numbers (1, 0, and -1), and say, only do a sum of it divided by the count, but I'd lose visibility of how many of each the person had.

Thoughts?  Other ways of accomplishing the same thing?

[theymos]

With PostgreSQL you could do this:

Code:

SELECT users.name, count(CASE WHEN (feedback.type='positive') THEN 1 ELSE NULL END) AS positive, count(CASE WHEN (feedback.type='negative') THEN 1 ELSE NULL END) AS negative FROM users JOIN feedback ON (feedback.name=users.name) GROUP BY users.name;

I don't know if that would work on MySQL.

[SgtSpike]

Thanks for the suggestion, I'll give it a shot!

[trentzb]

Post schema and sample data.

[davout]

Subqueries baby

Code:

SELECT 
  users.name, 
  (SELECT COUNT(*) FROM feedback f1 WHERE [it's positive] AND users.name = f1.username) AS positive,
  (SELECT COUNT(*) FROM feedback f2 WHERE [it's negative] AND users.name = f2.username) AS negative
FROM 
  users 

[SgtSpike]

davout, yeah I ended up going the subquery route on suggestion from a friend.

I thought the CASE method might be more efficient (which it might), but I couldn't get it to work right.  It kept clumping up the data into a single person's username.  I'll revisit the issue later on if speed of the query becomes a problem.