# Subtract 2 totals

Hi. I have a table that contains integers. I want to count all the occufrences of the Number 1 and count all the occurences of number 2 in the table and then subtract the occurences of Number 1s' from Number 2s'. Please help.

``````SELECT
SUM(CASE WHEN column_name = 2 THEN 1 ELSE 0 END) -
SUM(CASE WHEN column_name = 1 THEN 1 ELSE 0 END) AS num_2_minus_num_1
FROM dbo.table_name``````

Works wonderfully! Thank you.

You're welcome!

The question now is, do you understand how and why it works so you could do it again when you next need to do such a thing? And, as a bit of a sidebar, this technique is known as a "CROSS TAB".

hi

i tried to do this
i understood it differently

## this is what squealer meant Col1 1 1 2 4

i understood it like this
Col1
1345234
2241562
2345221

## count all 1's and 2's in 1345234 each row

Here's how i did this using tally table

Something different
Maybe knowledge purpose
Depends on who's looking how

drop create data ...
``````use tempdb
go

drop table data
go

create table data
(
num_data bigint
)
go

insert into data select 1567144233433331342
insert into data select 2672442314331221343
insert into data select 4561144233432331342
go

--select * from data
--go
``````
SQL ...
``````;WITH tally_cte
AS (SELECT N=1
UNION ALL
SELECT n + 1
FROM   tally_cte
WHERE  n + 1 < 20),
cte
AS (SELECT num_data,
Substring(Cast(num_data AS VARCHAR(100)), n, 1) AS num,
1                                               AS ok
FROM   tally_cte,
data
GROUP  BY num_data,
n)
SELECT num_data,
num,
Count(ok)
FROM   cte
WHERE  num IN( 1, 2 )
GROUP  BY num_data,
num
ORDER  BY num_data

go
``````
Result

I understand it better than I did before I asked the question, certainly.

@harishgg1 Thanks for response. The column is as your first example, ie just a column of either 0s' 1's or 2s' and so the answer supplied by Scott is peachy. Yours I'm intrigued by and am tinkering with in another context for the sake of learning. Thanks again.