# Multiple count queries in the one my result table

I have a table, mytable which has a numeric column myint.

I wish to get the number of rows that are above 10, above 8 but below 10, above 5 but below 8 and present them in on result table as:

above 10 | above 8 below 10 | above 5 below 8 27 | 17 | 300

I can get them vertical using a union all. Must I use a join to get them horizontal?

## Answers

SELECT SUM (CASE WHEN myint > 10 THEN 1 ELSE 0 END) AS 'above 10', SUM (CASE WHEN myint BETWEEN 8 AND 9 THEN 1 ELSE 0 END) AS 'above 8 below 10', SUM (CASE WHEN myint BETWEEN 5 AND 7 THEN 1 ELSE 0 END) AS 'above 5 below 8' FROM mytable

Change < to <= or > to >= if you wante to include uper or lower bounds

select (select count(*) from myTable where myint > 10) as "above 10", (select count(*) from myTable where myint > 8 and myint < 10) as "above 8 below 10", (select count(*) from myTable where myint > 5 and myint <8) as "above 5 below 8" from myTable

Try this:

SELECT SUM(CASE WHEN myInt > 10 THEN 1 ELSE 0 END) AS '>10', SUM(CASE WHEN myInt > 8 AND myInt < 10 THEN 1 ELSE 0 END) AS '(8-10)', SUM(CASE WHEN myInt > 5 AND myInt < 8 THEN 1 ELSE 0 END) AS '(5-8)' from mytable

- Note that you are not returning where myInt = 8 as it is excluded from all selections.

This is probably the fastest way of doing it.