CODEWITHSUNDEEP
sqlpostgresqlsumaggregateaverage
Alex
Alex

Reputation: 45

SQL SUM/AVG query exclude rows in postgres

I have a table storing results of questionnaires. The table has columns called:

q1,q2,q3,q4 ....

Valid values are positive 0 to 100. If a question is not answered the value is -1 so I'm ending up with rows:

       q1   q2   q3   q4  ...  
row 1  10  100   -1   50  
row 2  20   -1   20   20  
row 3  50   60  100   -1

I would like to aggregate q1 to q4 with the following results:
SUM:

q1 => 80  
q2 => 160  
q3 => 120  
q4 => 70

AVG:

q1 => 80 / 3  
q2 => 160 / 2  
q3 => 120 / 2  
q4 => 70 / 2

How can I achieve this?

Upvotes: 2

Views: 1848

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

Just replace -1 with NULL and use the aggregate functions on the columns directly:

SELECT sum(q1) AS q1_sum
     , sum(q2) AS q2_sum
     , sum(q3) AS q3_sum
     , sum(q4) AS q4_sum
     , count(q1) AS q1_ct
     , count(q2) AS q2_ct
     , count(q3) AS q3_ct
     , count(q4) AS q4_ct
     , round(avg(q1),2) AS q1_avg
     , round(avg(q2),2) AS q2_avg
     , round(avg(q3),2) AS q3_avg
     , round(avg(q4),2) AS q4_avg
FROM (
  SELECT NULLIF(q1, -1) AS q1
       , NULLIF(q2, -1) AS q2
       , NULLIF(q3, -1) AS q3
       , NULLIF(q4, -1) AS q4
  FROM   tbl
  ) t;

SQL Fiddle.

Why?
NULL values are automatically excluded from all of these aggregate functions.
It would be simpler to store non-existent values as NULL to begin with.

Upvotes: 4

shecode
shecode

Reputation: 1726

SQL is not optimised for averaging for each column like this. It's better for row groupings. However you can solve this by manually writing conditional pivot statements like this for each column:

    SELECT 
    SUM(CASE WHEN q1 > 0 THEN q1 ELSE 0 END)/
      SUM(CASE WHEN q1 > 0 THEN 1 ELSE 0 END) AS q1_avg,
    SUM(CASE WHEN q2 > 0 THEN q1 ELSE 0 END)/
      SUM(CASE WHEN q2 > 0 THEN 1 ELSE 0 END) AS q2_avg
    FROM table;

Upvotes: 0

Related Questions