CODEWITHSUNDEEP
sqlsql-server
dublintech
dublintech

Reputation: 17785

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?

Upvotes: 0

Views: 84

Answers (3)

Tom Chantler
Tom Chantler

Reputation: 14931

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.

Upvotes: 1

anouar.bagari
anouar.bagari

Reputation: 2104

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

Upvotes: 1

Jakub Konecki
Jakub Konecki

Reputation: 46008

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

Upvotes: 2

Related Questions