Using calculated column within SQL query-CodePudding

I need help with a subquery. I think I’m close but not quite sure. I want to use a calculated column within another calculation. In the example want to use “numerator” in the calculation of the “denominator”. I’m new to subqueries.

SELECT
   sum(case when denominator=1 and  q10=1 then 1 else 0 end) as numerator 
    FROM (
            SELECT sum(case when q5=1 and q6=1 then 1 else 0 end) as denominator 
  FROM datasource

q5	q6	q10
1	1	0
0	1	0
0	0	1

I want to calculate a “denominator” column where it’s 1 if q5 and q6 are equal to 1 or else “denominator”=0. Then calculate a “numerator” column if the calculated denominator column is 1 and q10 is 1 then “numerator”=1 or else “numerator”=0. You can ignore the sum command cause I just wanted to sum the columns at the end but for the purpose of the question it isn’t important

Update… I tried the below query as suggested and get the error “Expression not in GROUP BY key”

SELECT sum(case when denominator=1 and q10 = 1 then 1 else 0 end) as numerator 
FROM (
    SELECT q10,
           sum(case when q5=1 and q6=1 then 1 else 0 end) as denominator 
     FROM datasource) t

CodePudding user response：

I haven't added the aggregation part, but I think this will get you close to what you are trying to do.

SELECT case when denominator=1 and q10 = 1 then 1 else 0 end as numerator 
FROM (
    SELECT q10,
           case when q5=1 and q6=1 then 1 else 0 end as denominator 
     FROM datasource
     GROUP BY q10) t