Combine two SQL queries on one table
select count(case when data < 100 then 1 end) as Small, count(case when data >= 100 then 1 end) as Bigfrom TableAWith 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 Bigfrom TableA
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
DECLARE @tst TABLE ( val INT)INSERT INTO @tst (val)SELECT 10UNIONSELEcT 15UNION SELECT 20UNION SELECT 40UNIONSELECT 40000UNIONSELECT 50000UNION 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