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.


Need Your Help

How to authenticate users against a Windows AD?

java windows hibernate jsf authentication

I've a JSF-Hibernate web application. I need to authenticate users against a Windows AD and the web application should only allow logins from members of group X, otherwise it should redirect to an ...