CODEWITHSUNDEEP
sqlsql-serversql-server-2008
leyla azari
leyla azari

Reputation: 963

Sum all column value in a row in SQL Server

I have a table in SQL Server that has 34 columns.

I need sum all column values in a row in SQL Server.

Table : [CALEN]

Columns: 

YEAR_   | MONTH_  |D1 | D2 | D3 | D4 | D5 .... | D31 | Days
------------------------------------------------------------
1392    | 12      | 1 | 1  | 2  | 1  |  4  ... | 0   | 29

I want calc count columns that have 1 value Calc this query:

select [All_column value is 1 and Start With D] 
FROM [CALEN]   
WHERE YEAR_ = 1392 and MONTH_ = 12

Upvotes: 2

Views: 7235

Answers (4)

Jack
Jack

Reputation: 520

Try this:

select year,month,D1,D2,D3....D31,
((nullif(count(D1),''))+(nullif(count(D2),''))+.....
(nullif(count(D31),''))) as Total
group by year, month

Upvotes: 0

koriander
koriander

Reputation: 3258

You need to unpivot your table:

SELECT  YEAR_, MONTH_, DAY_, COUNT_
FROM    (SELECT * FROM [CALEN] WHERE YEAR_ = 1392 and MONTH_ = 12) CALEN_FILTER
UNPIVOT (COUNT_ FOR DAY_ IN (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10,
  D11, D12, D13, D14, D15, D16, D17, D18, D19,
  D20, D21, D22, D23, D24, D25, D26, D27, D28, D29,
  D30, D31)) AS CALEN_UNPIVOTED

Then you can easily make a common aggregate query, maybe easier puting the query above in a CTE, e.g,

WITH CALEN_U (YEAR_, MONTH_, DAY_, COUNT_) AS (
    SELECT  YEAR_, MONTH_, DAY_, COUNT_
    FROM    (SELECT * FROM [CALEN] WHERE YEAR_ = 1392 and MONTH_ = 12) CALEN_FILTER
    UNPIVOT (COUNT_ FOR DAY_ IN (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10,
      D11, D12, D13, D14, D15, D16, D17, D18, D19,
      D20, D21, D22, D23, D24, D25, D26, D27, D28, D29,
      D30, D31)) AS CALEN_UNPIVOTED
)
SELECT YEAR_, MONTH_, COUNT(DAY_)
FROM CALEN_U 
WHERE COUNT_ = 1
GROUP BY YEAR_, MONTH_

Edit: see SQLFiddle, using 10 days.

Upvotes: 4

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

Try this:

SELECT D1, D2, D3, ... ... ... D31, 
(D1 + D2 + ... ... ... D31) as 'Days'
FROM [CALEN]
WHERE YEAR_ = 1392 and MONTH_ = 12

Upvotes: 0

Hamidreza
Hamidreza

Reputation: 3118

You can do something like this:

SELECT 
CASE WHEN D1 = 1 THEN 1 ELSE 0 END +
CASE WHEN D2 = 1 THEN 1 ELSE 0 END +
CASE WHEN D3 = 1 THEN 1 ELSE 0 END +
CASE WHEN D4 = 1 THEN 1 ELSE 0 END +
.
.
.
CASE WHEN D31 = 1 THEN 1 ELSE 0 END AS TOTAL_D
FROM [CALEN]   
WHERE YEAR_ = 1392 AND MONTH_ = 12

SQL Fiddle

Upvotes: 0

Related Questions