CODEWITHSUNDEEP
sqloracle-databaseaggregatesummarize
Varun
Varun

Reputation: 1321

SQL summarise based on condition

Let's say I have a table called Table1 that contains three columns, VALUE, CODE and TYPE. The column VALUE contains scores ranging from 1-4 for different questions. The column CODE contains the code pertaining to the question. The column TYPE indicates whether the question is part of a paper (PAP) or online (ONL) survey.

Here's a glimpse of what the table looks like.

#Table1

VALUE    CODE    TYPE
-----   ------  ------ 
  2      Q2      ONL 
  1      Q2      PAP
  3      Q2      ONL
  4      Q2      ONL
  1      Q2      ONL
  1      Q2      ONL
  1      Q2      PAP
  2      Q2      ONL
  4      Q2      ONL
  2      Q2      ONL
  3      Q1      ONL
  1      Q1      ONL
  4      Q1      ONL
  3      Q1      ONL
      and so on...

My Objective

To summarize this table using an SQL query (Oracle) such that-

1) I first count all values that are either 1's OR 2's for CODE Q2

2) And divide that number by the total number of values for Q2

3) Group results by TYPE so that I have grouped results for both ONL and PAP.

Intended Outcome

So in our case, the result of this query would be -

TYPE      SCORE
PAP       0.625 (5/8)
ONL       1     (2/2)

My Attempt

As simple as this seems, I cannot seem to get the right value. I feel this requires a nested query to store the different values but I am unable to find the right example to guide me. Here's my incorrect attempt at it-

SELECT TYPE, VALUE/COUNT(VALUE) as SCORE FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE

Any help will be greatly appreciated.

Upvotes: 0

Views: 344

Answers (3)

Neville Kuyt
Neville Kuyt

Reputation: 29619

Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.

Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7

SELECT TYPE, 
COUNT(VALUE), 
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can simply do:

select type, avg(case when value in (1, 2) then 1.0 else 0 end) 
from table1
where code = 'Q2'
group by type;

Upvotes: 3

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

Here it is: 

select type, cnt_value/sum_value as score 
 FROM (
  SELECT type, count (value) as cnt_value , sum (value) as sum_value 
    FROM Table1
   WHERE
    CODE = 'Q2' AND
   VALUE in (1,2)
   GROUP by type
)

Upvotes: 1

Related Questions