Combine two SQL queries into one table

Question

I have tableA with different values:

  data ------ 10 15 20 40 40000 50000 60000 

In addition, I need to get some statistical information about this data (and I want to do this in one query), for example:

 select count(data) from tableA where data < 100 union all select count(data) from tableA where data >= 100 

As a result, I get

 (No column name) ---------------- 4 3 

But I want to get the results in one line, for example:

 Small | Big --------- 4 | 3 

How to do it? Is it possible?

Answer 1

 select count(case when data < 100 then 1 end) as Small, count(case when data >= 100 then 1 end) as Big from TableA 

On average, it will look like this.

 select avg(case when data < 100 then data end) as Small, avg(case when data >= 100 then data end) as Big from TableA 

Answer 2

Try a subquery instead of UNION ALL as follows:

 SELECT (SELECT COUNT(data) FROM tableA WHERE data < 100) AS Small, (SELECT COUNT(data) FROM tableA WHERE data >= 100) AS Big 

See this SQLFiddle

Answer 3

 DECLARE @tst TABLE ( val INT ) INSERT INTO @tst (val) SELECT 10 UNION SELEcT 15 UNION SELECT 20 UNION SELECT 40 UNION SELECT 40000 UNION SELECT 50000 UNION SELECT 60000 ;WITH Smalls AS ( SELECT COUNT(val) Small FROM @tst WHERE val < 100 ), Bigs AS( select count(val) Big from @tst where val >= 100 ) SELECT Small, Big FROM Smalls, Bigs 

Answer 4

 create table datatable ( data int ) insert into datatable values(10) insert into datatable values(15) insert into datatable values(20) insert into datatable values(40) insert into datatable values(40000) insert into datatable values(50000) insert into datatable values(60000) create table outputtable ( small int , big int ) insert into outputtable ( small, big ) select (select count(data) from datatable where data<100), (select count(data) from datatable where data>=100) select * from datatable select * from outputtable