Subtract 2 totals

1

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.

2 
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
3

Works wonderfully! Thank you.

4

You're welcome!

5

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".

6

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
:slight_smile:
:slight_smile:

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

image
image.png674×501 7.13 KB

7

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

8

@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.