CODEWITHSUNDEEP
mysqlsql
Miguel Stevens
Miguel Stevens

Reputation: 9191

One query for multiple where?

I have a Query which gets the average score from the answers table, And groups it by the category which the question is in.

This query gets the desired result for answers where the coach_id = 0 This is the desired result in which for every coach_id i get another row

enter image description here

Now my question is: Is it possible to also get the same answers from the same table ( so almost the same query ) but where the coach_id = 1 or coach_id = 2.. in the same query?##

This is my query

SELECT
    ROUND((SUM(score) / COUNT(vragen.id) * 10),1) as score
FROM antwoorden
JOIN vragen
    ON vragen.id = antwoorden.vraag_id
JOIN categorieen
    ON categorieen.id = vragen.categorie_id
WHERE antwoorden.werknemer_id = 105 AND antwoorden.coach_id = 0
GROUP BY categorieen.id

Any ideas? Thanks!

Upvotes: 0

Views: 60

Answers (3)

Hector Ordonez
Hector Ordonez

Reputation: 1104

What you want is a conditional sum, I think.

Column score_0, that gets the average score for coach_id = 0. Column score_1, that gets the average score for coach_id = 1.

The count will not work neither, as count ... counts everything! Both coach_id 0 and 1. So you'll have to use a conditional sum there, too.

Besides you'll need the coach_id filter suggested by Neville K.

So:

SELECT
    ROUND((
        SUM(CASE WHEN antwoorden.coach_id = 0 THEN score ELSE 0 END) /
        SUM(CASE WHEN antwoorden.coach_id = 0 THEN 1 ELSE 0 END) * 10
    ), 1) as score_0,
    ROUND((
        SUM(CASE WHEN antwoorden.coach_id = 1 THEN score ELSE 0 END) /
        SUM(CASE WHEN antwoorden.coach_id = 1 THEN 1 ELSE 0 END) * 10
    ), 1) as score_1
FROM antwoorden
JOIN vragen
    ON vragen.id = antwoorden.vraag_id
JOIN categorieen
    ON categorieen.id = vragen.categorie_id
WHERE antwoorden.werknemer_id = 105 AND antwoorden.coach_id IN (0,1)
GROUP BY categorieen.id

I think this is what you meant.

Upvotes: 1

jeremycole
jeremycole

Reputation: 2761

Since you haven't provided your table schemas I would have a hard time writing the query for your actual example. What you want is the SUM(IF(...)) pattern, aggregating on a conditional:

SELECT
  foo_id,
  SUM(IF(bar_id = 1, baz, 0)) as sum_baz_bar_1,
  SUM(IF(bar_id = 2, baz, 0)) as sum_baz_bar_2,
  SUM(IF(bar_id = 3, baz, 0)) as sum_baz_bar_3
FROM table
WHERE ...
GROUP BY foo_id

You need to think carefully about your aggregation functions when using this pattern, especially with COUNT or other functions that deal with the presence of a value (such as 0) rather than the value of it.

If you post your table schemas (SHOW CREATE TABLE) or even better set up a sample data set on sqlfiddle.com, I would be happy to help show how to do it with your actual schemas.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29619

Yes.

You can use the in clause

... antwoorden.coach_id in (0, 1, 2)

Upvotes: 0

Related Questions