CODEWITHSUNDEEP
sqlplsqlinformix
app
app

Reputation: 13

Combine results of multiple sql queries in multiple columns all alone

I have multiple queries, which produces just a count (one cell only) in one column. I need to combine those columns.

Suppose the queries are:

select count (*) from address where city = NULL as citycount;
select count (*)  from address where countrycode = 4 as countrycount;
select count (*) from address;

Above queries will return the results:

citycount

40

countrycount

50

count(*)

400045

I want to combine above queries in such a manner so that result will be:

 citycount countrycount  count(*)

    40        50          400045

This means the columns will get combined . I have more than 30 queries. Thanks in advance

Upvotes: 1

Views: 123

Answers (3)

singhswat
singhswat

Reputation: 906

Try Something like this... hope it helps

CREATE TABLE #TEST
(
ID INT IDENTITY(1,1) NOT NULL,
Cnt INT NOT NULL,
DescTableName VARCHAR(100) NULL
)

INSERT INTO #TEST (Cnt, DescTableName)
SELECT COUNT(*) AS Cnt, 'citycount' AS DescTableName FROM dbo.[Subscriptions] 

INSERT INTO #TEST (Cnt, DescTableName)
SELECT COUNT(*) AS Cnt, 'countrycount' AS DescTableName FROM dbo.ReportSchedule

INSERT INTO #TEST (Cnt, DescTableName)
SELECT COUNT(*) AS Cnt, 'address' AS DescTableName FROM msdb.dbo.sysjobs 

SELECT * FROM #TEST

DROP TABLE #TEST

Upvotes: 0

Vladislav Latish
Vladislav Latish

Reputation: 317

select 
(select count (*) from address where city IS NULL) as citycount,
(select count (*) from address where countrycode = 4) as countrycount,
count(*)
from address

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28930

Use SUM with CASE Expression: 

 select 
    sum(case when city = NULL then 1 else 0 end ) as citycount,
    sum(case when countrycode=4 then 1 else 0 end ) as countrycount,
    count(*) as countt
    from address

Upvotes: 2

Related Questions