MySQL query - get sum of counts depending on column

Question

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...

John Woo · Accepted Answer · 2012-08-14 12:19:16Z

2

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

answered Aug 14, 2012 at 12:19

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Horen Over a year ago

that's pretty nice already except that the scores come from php constants and not another table. should be easy enough to adjust though. i'll give it a try

John Woo Over a year ago

you can create table for your constants though :)

Horen Over a year ago

I could - but that would mean, I would have to adjust scores in two places whenever scores would be changed - so I'd prefer storing them in one place.

pomaxa Over a year ago

it is better to store them in db, and just fetch them from php on each load. cuz storing it in future in your php file it's not a good idea.

John Woo Over a year ago

@Horen you would have more work to do if the constants are on your PHP file. but it still depends within you :)

Luke Adamczewski · Accepted Answer · 2012-08-14 12:37:36Z

1

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

edited Aug 14, 2012 at 12:37

answered Aug 14, 2012 at 12:13

Luke Adamczewski

3953 silver badges14 bronze badges

4 Comments

Horen Over a year ago

there is no such column as score

Luke Adamczewski Over a year ago

but score is based on which criteria on action value?

Horen Over a year ago

yes. the score comes from php constants. so they have to be in the query already.

Luke Adamczewski Over a year ago

try now, it doesn't need grouping by action but only by question to get total score. I forgot to add comparison in case because all values was equals to 2 without this

pomaxa · Accepted Answer · 2012-08-14 12:12:50Z

0

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)

answered Aug 14, 2012 at 12:12

pomaxa

1,74616 silver badges26 bronze badges

1 Comment

Horen Over a year ago

the score comes from php constants and has to be added to the query. For now we could hard code it

Collectives™ on Stack Overflow

MySQL query - get sum of counts depending on column

3 Answers 3

SQLFiddle Demo

5 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related