SQL Bring Where Clause to New Columns-CodePudding

I'm running this SQL on a SQL Server and it runs fine:

SELECT
    X.benefit,
    AVG(X.weight) AS W
FROM  
    (SELECT
         benefit,
         weight,
         comp
     FROM
         Segment_Responses AS a
     INNER JOIN 
         Segment_Descriptors AS b ON a.resp_id = b.resp_id
     WHERE
         b.comp = 0) AS X
GROUP BY
    X.benefit

My question is this...while I could run this with b.comp=0 (and get the benefits and average weights) and then again with b.comp=1 (b.comp is a boolean), is there a way to construct the SQL so that there are 3 outputs (benefits, average weight when b.comp=0 and average weight when b.comp=1). Seems this should be possible - I just don't know how to do it. Thanks.

CodePudding user response：

You could query the average of a couple of case expressions:

SELECT
    X.benefit,
    AVG(CASE comp WHEN 1 THEN X.weight END) AS comp_1_avg,
    AVG(CASE comp WHEN 0 THEN X.weight END) AS comp_0_avg
FROM  
    (SELECT
         benefit,
         weight,
         comp
     FROM
         Segment_Responses AS a
     INNER JOIN 
         Segment_Descriptors AS b ON a.resp_id = b.resp_id) AS X
GROUP BY
    X.benefit

CodePudding user response：

Yes, try this

  SELECT
   X.benefit,
   avg(case when b.comp=0 then X.weight end) AS W, 
  avg(case when b.comp=1 then X.weight end) AS W1, 
  FROM (
  SELECT
   benefit,
 weight,
 comp
  FROM
 Segment_Responses AS a
 INNER JOIN Segment_Descriptors AS b ON a.resp_id = 
 b.resp_id
   ) AS X
GROUP BY
 X.benefit