CODEWITHSUNDEEP
sqlsql-serverselectgrouping
Alex Galvão
Alex Galvão

Reputation: 37

How can I aggregate a column of bits?

I have this query:

SELECT 
    MAX(P.Descricao) AS Name,  
    SUM(FC.Credito) AS Sales,
    CAST(CASE
            WHEN FC.Troco = 1 THEN SUM(FC.Debito)
            ELSE 0 
         END AS decimal(10, 2)) AS Purchases
FROM 
    FluxoCaixa FC 
INNER JOIN
    Pagamento P ON FC.ID_Pagamento = P.Pagamento

I'm getting this error:

Column 'FluxoCaixa.Troco' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I know that an error is returning because the column FC.Troco is not aggregated, but how do I aggregate a column whose values ​​are bit?

In the CASE-WHEN, I want to SUM(FC.Debito) case FC.Troco be 1(true).

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want conditional aggregation:

SELECT MAX(P.Descricao) AS Name,  SUM(FC.Credito) AS Sales,
       SUM(CASE WHEN FC.Troco = 1 THEN FC.Debito ELSE 0 END) AS Purchases
FROM FluxoCaixa FC INNER JOIN
     Pagamento P
     ON Fc.ID_Pagamento = P.Pagamento;

Upvotes: 2

Related Questions