CODEWITHSUNDEEP
mysql
Horen
Horen

Reputation: 11382

MySQL query - get sum of counts depending on column

I have a mysql table with actions and question_ids. Each action comes with a score like this:

     ACTION       | SCORE
downvote_question |  -1
upvote_question   |  +1
in_cardbox        |  +2

I want to query for the question with the highest score but I can't figure it out.

http://sqlfiddle.com/#!2/84e26/15

So far my query is:

SELECT count(*), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

which gives me every question_id with all its accumulated actions.

What I want is this:

QUESTION_ID | SCORE
     2      |   5
     1      |   4
     3      |   1
     4      |   1
     5      |   1

I can't figure it out - I probably need subqueries, JOINS or UNIONS...

Upvotes: 3

Views: 240

Answers (3)

Luke Adamczewski
Luke Adamczewski

Reputation: 395

You should replace count(*) with sum(l1.score) because sum will add all values based on group by statement

SELECT sum(l1.score), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

With constant scores works on SQL Fiddle (with grouping by question):

SELECT 
sum(
  CASE WHEN l1.action = 'downvote_question' THEN -1 
  WHEN l1.action = 'upvote_question' THEN 1 
  ELSE 2 END
) score,
l1.question_id
FROM `log` l1
GROUP BY l1.question_id

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Maybe you can try this one.

SELECT  a.question_id, sum(b.score) totalScore
FROM   `log` a INNER JOIN scoreValue b
          on a.`action` = b.actionname
group by a.question_id
ORDER BY totalScore DESC

SQLFiddle Demo

Upvotes: 2

pomaxa
pomaxa

Reputation: 1746

SELECT sum(SCORE), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

is it what you want to?

upd: in your code on fidel, there is no such column as score, but i think it wont be a problem to create a tabel with action | score and join it to sum(score)

Upvotes: 0

Related Questions