1

I have a query that calculates an average of rows. The average value of a single row consists of the values that are not null/empty. Well this works. What I want now is to only count the row if its's average is bigger than a certain number. Now it's using all the rows. How can I get this done?

id | p_cijfer | b_cijfer | c_cijfer | c_id
----------------------------------------------
1  | 4        | 4        |    0     |   3   average = 4
----------------------------------------------
2  | 8        | 6        |    6     |   3   average = 6.7
----------------------------------------------
3  | 10       | 6        |    8     |   3   average = 8
----------------------------------------------
1  | 4        | 4        |    0     |   1   average = 4
----------------------------------------------

Now I only want to count the avarage of row with an higher average then 6. Wich will result in only counting record 2 and 3. Output must than be the average of id's 2 & 3, which average = (6.7+8)/2=7.5

select 

AVG(((reviews.prijs_cijfer+
 reviews.planning_cijfer+
 reviews.betrouwbaarheid_cijfer +
 reviews.communicatie_cijfer +
 reviews.kennis_cijfer +
 reviews.innovatie_cijfer
) /
nullif(
  case when prijs_cijfer=0 then 0 else 1 end +
  case when planning_cijfer=0 then 0 else 1 end +
  case when betrouwbaarheid_cijfer=0 then 0 else 1 end +
  case when communicatie_cijfer=0 then 0 else 1 end +
  case when kennis_cijfer=0 then 0 else 1 end +
  case when innovatie_cijfer=0 then 0 else 1 end , 0)))
from reviews
waterschaats
  • 994
  • 3
  • 18
  • 32
  • Not sure what you need. Can you please provide sample data (two columns will be enough) and the desired result. – PM 77-1 Jun 26 '15 at 17:27

1 Answers1

2

You can apply a filter by using a subquery.

select c_id, avg(rows.rowavg) filteredavg, count(rows.rowavg) affecting
from (
    select
        c_id c_id,
        ((reviews.prijs_cijfer+
        reviews.planning_cijfer+
        reviews.betrouwbaarheid_cijfer +
        reviews.communicatie_cijfer +
        reviews.kennis_cijfer +
        reviews.innovatie_cijfer
        ) /
        nullif(
        case when prijs_cijfer=0 then 0 else 1 end +
        case when planning_cijfer=0 then 0 else 1 end +
        case when betrouwbaarheid_cijfer=0 then 0 else 1 end +
        case when communicatie_cijfer=0 then 0 else 1 end +
        case when kennis_cijfer=0 then 0 else 1 end +
        case when innovatie_cijfer=0 then 0 else 1 end , 0)) rowavg
    from reviews
) rows
where rows.rowavg > 6
group by c_id
Bert
  • 119
  • 8
  • Thanks, but when I run the query is says: 'Every derived table must have its own alias, – waterschaats Jun 26 '15 at 18:09
  • Briljant, I have an additional question. What if I want that for c_id = 3 AND c_id = 1 – waterschaats Jun 26 '15 at 18:34
  • add a where clause to the subquery: ... from reviews where c_id in ('1', '3') ... – Bert Jun 26 '15 at 18:41
  • I mean that I want the result of c_id=3 and c_id=1, so that I have 2 results. If I use ... from reviews where c_id in ('1', '3') ... it only gives 1 result, – waterschaats Jun 26 '15 at 21:51
  • Then you need group by at the bottom. See [this question](http://stackoverflow.com/questions/10702546/sql-query-with-avg-and-group-by) for an example. – Bert Jun 26 '15 at 22:38
  • And is it possible to get the number of affected rows? I mean the row whose average are bigger than 6 – waterschaats Jun 26 '15 at 22:38
  • 1
    affecting rows: use `count(rows.rowavg)` – Bert Jun 26 '15 at 22:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81686/discussion-between-bert-and-waterschaats). – Bert Jun 26 '15 at 22:45