SQL group by syntax

I have the follow select:

SELECT
  COUNT(a.int_re_usu) AS qtd,
  a.txt_resposta,
  b.txt_marca,
  (
    SELECT
      CASE
        WHEN a.txt_resposta IS NULL
        T开发者_运维技巧HEN
          CASE
            WHEN a.bit_opcao = 1
            THEN 'Sim'
            ELSE 'Não'
          END
        ELSE a.txt_resposta
      END
  ) AS Answer
  FROM tb_questionario_voar_resposta a
  INNER JOIN tb_questionario_voar b ON a.int_id_questionario = b.int_id_questionario
  GROUP BY b.txt_marca, Answer

As you can see,ive a select case that deal with two columns.

When i try to run this query i get an error saying: Invalid column name 'Answer'.

My question is how to run this query group by the "Select case" column named "Answer".

---

Here's a more readable version:

SELECT 
    COUNT(a.int_re_usu) AS qtd, 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN 'Sim' 
                ELSE 'Não' 
            END 
        ELSE a.txt_resposta 
     END) AS answer, 
    a.txt_resposta 
FROM   
    tb_questionario_voar_resposta a 
    INNER JOIN tb_questionario_voar b 
        ON a.int_id_questionario = b.int_id_questionario 
GROUP BY 
    b.txt_marca, 
    answer 

The first problem is that the GROUP BY doesn't include txt_resposta.

The second problem is that you can't "GROUP BY answer" because answer is not in the source tables, but calculated. As suggested above, you'd be better off splitting the subquery into a separate WITH clause, but if you really want to do it in one fell swoop, it would be something (rather ugly) like this:

SELECT 
    COUNT(a.int_re_usu) AS qtd, 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN 'Sim' 
                ELSE 'Não' 
            END 
        ELSE a.txt_resposta 
     END) AS answer, 
    a.txt_resposta 
FROM   
    tb_questionario_voar_resposta a 
    INNER JOIN tb_questionario_voar b 
        ON a.int_id_questionario = b.int_id_questionario 
GROUP BY 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN 'Sim' 
                ELSE 'Não' 
            END 
        ELSE a.txt_resposta 
     END),
     a.txt_resposta 

or a little more readably, with a WITH clause:

WITH temp(usu, txt_marca, answer, txt_resposta) AS (
    SELECT 
        a.int_re_usu,
        b.txt_marca, 
        (SELECT CASE 
            WHEN a.txt_resposta IS NULL THEN 
                CASE 
                    WHEN a.bit_opcao = 1 THEN 'Sim' 
                    ELSE 'Não' 
                END 
            ELSE a.txt_resposta 
         END), 
        a.txt_resposta 
)
SELECT
    COUNT(usu) as qtd,
    txt_marca,
    answer,
    txt_resposta
FROM
    temp
GROUP BY
    txt_marca,
    answer,
    txt_resposta

---

You have to specify the same case statement in both the select and the group by parts of the query.

---

Please check the spelling of 'Anwser', you named it as 'Awnser' in the code..

---

You could try:

select  count(int_re_usu) as qtd, 
    txt_marca, Answer. txt_resposta 
From 
    (select  
        a.int_re_usu, 
        b.txt_marca, 
        case when a.txt_resposta is null 
        then  
        case when a.bit_opcao = 1 
        then 
        'Sim' 
        else 
        'Não' 
        end 
        else 
        a.txt_resposta 
        end as Answer, 
        a.txt_resposta 
    from  
    tb_questionario_voar_resposta a 
    inner join 
    tb_questionario_voar b 
    on 
    a.int_id_questionario 
    = 
    b.int_id_questionario ) c
group by 
txt_marca, 
Answer,
txt_resposta 

---

You may want to consider creating a view which performs the sub-query case statement. Then select from the view with grouping.