CODEWITHSUNDEEP
sql-serversql-server-2012sql-server-2008-r2
Geeme
Geeme

Reputation: 415

SQL single row result set from multiple sql

I have table like below

create table #temp
( 
 a int null,
b int null,
c int null,
d int null
)

insert into #temp values (0,0,0,0)
insert into #temp values (0,1,0,0)
insert into #temp values (0,0,0,1)
insert into #temp values (0,1,0,0)

i tried

select * from(
(select count(*)  a from #temp where a=1 group by a) dt1 cross join
(select count(*)  b from #temp where b=1 group by b) dt2 cross join
(select count(*)  c from #temp where c=1 group by c) dt3 cross join
(select count(*)  d from #temp where d=1 group by d) dt4 
)

but not getting any output

i want to get output like below

a  b  c  d
0  2  0  1

how to i achieve this?

Upvotes: 1

Views: 67

Answers (3)

user7715598
user7715598

Reputation: 

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DRop Table #TEMP

CREATE TABLE #TEMP
( 
A INT NULL,
B INT NULL,
C INT NULL,
D INT NULL
)

INSERT INTO #TEMP VALUES (0,0,0,0)
INSERT INTO #TEMP VALUES (0,1,0,0)
INSERT INTO #TEMP VALUES (0,0,0,1)
INSERT INTO #TEMP VALUES (0,1,0,0)

SELECT * FROM #TEMP

SELECT SUM(A)A,SUM(B)B,SUM(C)C,SUM(D)D FROM #TEMP

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

If the values are only 0 and 1 then use can simply use SUM:

select sum(a), sum(b), sum(c), sum(d) from #temp;

If there can be more other values, and you want to count only 1 then use can use sum on case:

select sum(case when a = 1 then 1 else 0 end),
    sum(case when b = 1 then 1 else 0 end),
    sum(case when c = 1 then 1 else 0 end),
    sum(case when d = 1 then 1 else 0 end)
from #temp;

Upvotes: 1

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

Try this:

DECLARE @temp TABLE( a int , b int , c int , d int )
insert into @temp values (0,0,0,0),(0,1,0,0),
(0,0,0,1),(0,1,0,0)

SELECT SUM(a) a,SUM(b) b,SUM(c) c,SUM(d) d FROM @temp

Hope it helps. :)

Upvotes: 3

Related Questions