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.
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".
i tried to do this
i understood it differently
this is what squealer meant
i understood it like this
count all 1's and 2's in 1345234
Here's how i did this using tally table
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
;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
I understand it better than I did before I asked the question, certainly.