How to Use Sum for Bit Columns

How can I use SUM for bit columns?

SELECT SUM(CAST(bitColumn AS INT))
FROM dbo.MyTable

need to cast into number

or another solution -

SELECT COUNT(*)
FROM dbo.MyTable
WHERE bitColumn = 1

SQL : How to sum multiple bit columns in a row

Cast the bit to Int and do the arithmetic operation

select id,cast([Ok_1112] as Int)+cast([Ok_1213] as Int)+...
From yourtable

Group BY while getting SUM of Bit columns

If data is lost after the JOINS, possible reason is that , either tbl_Controls or tbl_Default_Controls or tbl_User_Profile is missing, the equivalent data from tbl_Results.

Can you try this query. I commented out tbl_Default_Controls as it is not used in where condition/Select

SELECT      P.[first_name] + ' ' + P.[last_name] AS full_name,
            SUM(CASE WHEN CONVERT(varchar(MAX),DecryptByKey(R.[default_value_binary])) = 'YES' THEN 1 ELSE 0 END) AS number_of_dnc_ver,
            P.[user_profile_id]
FROM        tbl_Results R
INNER JOIN  tbl_Controls C ON R.control_id = C.control_id
--INNER JOIN    tbl_Default_Controls DC ON C.default_control_id = DC.default_control_id
INNER JOIN  tbl_User_Profile P ON P.user_profile_id = R.user_profile_id
WHERE       (P.application_id = 3707 AND C.control_name LIKE 'rbl_ContactEmployer_%')
GROUP BY    P.[user_profile_id],
            P.[first_name] + ' ' + P.[last_name]

Aggregate bit column simulating AND operator

Compare the sum with count if you have only 1 and 0 in that column:

select Id, case when sum(BitValue) < count(Id) then 0 else 1 end as BitValue 
from mytable group by Id;

Sql fiddle.

Thanks for @Larnu's useful comment. I put up another sql fiddle to reflect his concern.

How to sum columns in sql

Try this :

WITH list AS (
select nome_equipa, 
sum(case when (resultado_visitado = resultado_visitante) and jogo.equipa_id_equipa = id_equipa or (resultado_visitante = resultado_visitado) and jogo.equipa_id_equipa1 = id_equipa  then 1 else 0 end ) as empates,
sum(case when (resultado_visitado > resultado_visitante) and jogo.equipa_id_equipa = id_equipa or (resultado_visitante > resultado_visitado) and jogo.equipa_id_equipa1 = id_equipa  then 1 else 0 end ) as vitorias,
sum(case when (resultado_visitado < resultado_visitante) and jogo.equipa_id_equipa = id_equipa or (resultado_visitante < resultado_visitado) and jogo.equipa_id_equipa1 = id_equipa  then 1 else 0 end ) as derrotas
from jogo, equipa
group by nome_equipa
order by vitorias desc
)
SELECT nome_equipa
     , empates
     , derrotas
     , vitorias
     , (empates*1 + derrotas*0 + vitorias*3) as pontos
  FROM list ;

which is equivalent to sticky bit's second option.

Weighted sum of a column vector and a derived bit vector

Below is for BigQuery Standard SQL an dis generic enough to not depend on number of buyers as well as naming for price and size fields. The only expectation is for all prices go first and then all respective sizes as it is in your example. Also i assume all numbers are integers (as in example in question) but this can be adjust to deal with FLOATs

#standardSQL
WITH t_ext AS (
  SELECT * EXCEPT(arr), 
    ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
    ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
    (SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
  FROM (
    SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
    FROM `project.dataset.table` t
  )
)
SELECT * EXCEPT(prices, sizes), 
  (SELECT SUM(size)
    FROM UNNEST(prices) price WITH OFFSET
    JOIN UNNEST(sizes) size WITH OFFSET
    USING(OFFSET) 
    WHERE price = bestPrice
  ) AS bS
FROM t_ext  

The only what you need to change in above query is number of buyers - in below expressions (in those below - 4 can be replaced with ARRAY_LENGTH(arr) / 2

WHERE OFFSET < 4
WHERE OFFSET >= 4
WHERE OFFSET < 4

For example, for below dummy data (4 buyers)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 pA, 2 pB, 3 pC, 4 pD, 1 sA, 1 sB, 1 sC, 5 sD UNION ALL
  SELECT 1, 4, 2, 4, 1, 6, 1, 5 UNION ALL
  SELECT 4, 4, 2, 1, 7, 1, 1, 1
), t_ext AS (
  SELECT * EXCEPT(arr), 
    ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
    ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
    (SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
  FROM (
    SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
    FROM `project.dataset.table` t
  )
)
SELECT * EXCEPT(prices, sizes), 
  (SELECT SUM(size)
    FROM UNNEST(prices) price WITH OFFSET
    JOIN UNNEST(sizes) size WITH OFFSET
    USING(OFFSET) 
    WHERE price = bestPrice
  ) AS bS
FROM t_ext

result is

Row pA  pB  pC  pD  sA  sB  sC  sD  bestPrice   bS   
1   1   2   3   4   1   1   1   5   4           5    
2   1   4   2   4   1   6   1   5   4           11   
3   4   4   2   1   7   1   1   1   4           8    

Add a SUM column of other columns from PIVOT

you can use window function to achieve to get row wise sum. I assumed that the unique identifier in your rows is Instrument so the window function will be

sum()over(partition by Instrument) as 'Total_Amount'

Resulted query:

SELECT [Date],
       [Type],
       Typology,
       Instrument,
       Market,
       Curve,
       ISNULL([6M], 0) AS [6M],
       ISNULL([1Y], 0) AS [1Y],
       ISNULL([2Y], 0) AS [2Y],
       ISNULL([3Y], 0) AS [3Y],
       ISNULL([4Y], 0) AS [4Y],
       ISNULL([5Y], 0) AS [5Y],
       ISNULL([6Y], 0) AS [6Y],
       ISNULL([7Y], 0) AS [7Y],
       ISNULL([8Y], 0) AS [8Y],
       ISNULL([9Y], 0) AS [9Y],
       ISNULL([10Y], 0) AS [10Y],
       ISNULL([11Y], 0) AS [11Y],
       ISNULL([12Y], 0) AS [12Y],
       ISNULL([15Y], 0) AS [15Y],
       ISNULL([20Y], 0) AS [20Y],
       ISNULL([25Y], 0) AS [25Y],
       ISNULL([30Y], 0) AS [30Y],
       ISNULL([40Y], 0) AS [40Y],
       Total_Amount
FROM (SELECT [Date],
             Typology,
             Instrument,
             Market,
             Type,
             Curve,
             Pillar,
             Amount,
             sum(Amount)over(Partition by Instrument) as 'Total_Amount'
      FROM tblActivePivotSensiBondDaily
      WHERE CONVERT(varchar(8), [Date], 112) = '20220525'
        AND type = 'CS01') source
PIVOT (SUM(Amount)
       FOR Pillar IN ([6M], [1Y], [2Y], [3Y], [4Y], [5Y], [6Y], [7Y], [8Y], [9Y], [10Y], [11Y], [12Y], [15Y], [20Y], [25Y], [30Y], [40Y])) pillars
ORDER BY Instrument;

---

Related Topics