CODEWITHSUNDEEP
sqlsql-server
qehgt
qehgt

Reputation: 2990

Combine two SQL queries on one table

I have a tableA with different values: 

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

Also, I need to get some statistic information on that data (and I want to do it in one query), for example:

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

As result, I receive 

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

But I want to receive results in one row, like this:

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

How to do it? Is it possible?

Upvotes: 9

Views: 9217

Answers (4)

user1613212
user1613212

Reputation: 93

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

With average it would 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

Upvotes: 11

Himanshu
Himanshu

Reputation: 32612

Try sub-query instead of UNION ALL like this:

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

See this SQLFiddle

Upvotes: 11

Mr Moose
Mr Moose

Reputation: 6354

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

Upvotes: 6

Related Questions