Counting Rows where values can be stored in multiple columns

Question

I have data that I need to count that can be stored in 2 separate columns. Here's an example

| id | col1 | col2 |
|----|------|------|
| 1 | 1 | 1 |
| 2 | 1 | 0 |
| 3 | 0 | 1 |
| 4 | 2 | 0 |

EDIT: I had a change from the user. They want to count ever instance where 1 appears in both columns.

I need the count for 1 to return 4 (as 3 rows have a 1 in col1 and/or col2)

Here is the query we have now. Problem is it is not counting when both columns contain the same value. When that happens we need it to return a count of 2 for that row

SELECT
 tb1.PARENTID, tb1.PARENTACCOUNT,tb2.TYPE, 
 tb1.USERCODE1,tb1.USERCODE2,tb1.USERDATE1,tb1.ProcessDate
FROM
 table1 as tb1
 inner JOIN table2 as tb2 on tb2.PARENTACCOUNT = tb1.PARENTACCOUNT
 AND tb2.ID = tb1.PARENTID
INNER JOIN 
(SELECT 
 MAX(ProcessDate) AS MaxDate, 
 PARENTACCOUNT,
 ID
FROM 
 table2
GROUP BY 
 PARENTACCOUNT,
 ID) AS t2
ON 
 (tb2.PARENTACCOUNT = t2.PARENTACCOUNT AND 
 tb2.ProcessDate = t2.MaxDate)
WHERE
 tb1.TYPE = '1'
 AND tb2.TYPE IN (1,2,3)
 AND MONTH(tb1.USERDATE1) = 1
 AND YEAR(tb1.USERDATE1) = 2023
 AND (@empnum IN (tb1.USERCODE1,tb1.USERCODE2))
 AND tb1.ProcessDate = t2.MaxDate
GROUP BY 
 tb1.PARENTID,tb1.PARENTACCOUNT,tb2.TYPE,
 tb1.USERCODE1,tb1.USERCODE2,tb1.USERDATE1,
 tb1.ProcessDate, MONTH(st.USERDATE1)

• Sean Lange: You need to explain what you want here. Your explanation is too vague for others to understand.
• Martin Smith: Where does the 3 come from? Is it because there are 3 distinct rows where 1 in (col1, col2)?
• David Schmidt: Sean, I'm looking for a return of the count of rows where 1 occurs, even if it's in Col1 and col2. In this example I want the returned count to be 3.
• David Schmidt: Martin, yes that is what I'm trying to do. Thank you!
• David Schmidt: Martin, Thank you for the updated answer. I'm having trouble getting the syntax to work with the main query we have to work with. Would you be so kind as to offer some assistance. I have updated my post with the additional query information. Thank you in advance

Answer 1

A CROSS APPLY equivalent of Martin's UNPIVOT suggestion, with the exact same outcome:

SELECT
 COUNT(*)
FROM
 dbo.YourTable AS t
 CROSS APPLY (VALUES (t.col1), (t.col2)) AS v (val)
WHERE
 v.val = 1
;

Answer 2

This requirement might be a bit of a code smell and indicate that your table is not in first normal form.

But anyway for the original requirement you can do

SELECT COUNT(*)
FROM YourTable
WHERE 1 in (col1, col2)

And for the edited requirement

SELECT COUNT(*)
FROM YourTable
UNPIVOT (val FOR Col IN (col1, col2)) U
WHERE val = 1

Answer 3

A different approach, which might or might be not easier to combine into your actual query:

Original request:

select
 sum(case when col1 = 1 or col2 = 1 then 1 else 0 end) as Total
from YourTable
;

Updated request:

select
 sum(case col1 when 1 then 1 else 0 end
 + case col2 when 1 then 1 else 0 end) as Total
from YourTable
;

---

On second thought (noticing "we need it to return a count of 2 for that row" only now):

select
 id,
 col1,
 col2,
 sum(case col1 when 1 then 1 else 0 end
 + case col2 when 1 then 1 else 0 end) as Counting1InCol1AndCol2,
 sum(case col1 when 1 then 1 else 0 end
 + case col2 when 1 then 1 else 0 end)
 over (partition by null) as OverallCount
from YourTable
group by id, col1, col2
;

This one returns the count per individual row - and as a bonus the overall count using a window function.

See it in action: SQL Fiddle.

Please comment, if and as this requires adjustment / further detail.