Re: SQL group by a range?

From: Ranga Chakravarthi <ranga_at_removethis.cfl.rr.com>
Date: Thu, 28 Feb 2002 04:26:20 GMT
Message-ID: <Mjif8.4262$1p6.997688_at_typhoon.tampabay.rr.com>


Oh, if you want the counts to appear as rows instead of columns, just use:

SELECT CASE WHEN grade BETWEEN 90 AND 100 THEN '90 to 100' WHEN grade BETWEEN 80 AND 89 THEN '80 to 89' .
.
WHEN grade BETWEEN 0 AND 9 THEN '0 to 9' END as "Grade range",
count(1) as "Number of Students"
FROM
<table>
GROUP BY
CASE WHEN grade BETWEEN 90 AND 100 THEN '90 to 100' WHEN grade BETWEEN 80 AND 89 THEN '80 to 89' .
.
WHEN grade BETWEEN 0 AND 9 THEN '0 to 9' END "Ranga Chakravarthi" <ranga_at_removethis.cfl.rr.com> wrote in message news:tfif8.4225$1p6.988774_at_typhoon.tampabay.rr.com...
> You can use CASE like this:
>
> SELECT SUM(CASE WHEN grade BETWEEN 90 AND 100 THEN 1 ELSE 0 END) as "90 to
> 100",
> SUM(CASE WHEN grade BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "80
> to 89",
> etc.
> FROM <table>
>
> "Neothinker" <neo_thinker_at_hotmail.com> wrote in message
> news:d351ed4f.0202261405.7a3ef902_at_posting.google.com...
> > I am trying to create a query that will tally the number of students
> > with a grade from 90 to 100, 80 to 89, 70 to 79 ,... etc. So if I have
> > a table that just had a "STUDENT" column and a "GRADE" column, what
> > generic SQL could I use? As far as I know, the GROUP BY only accepts a
> > column name, not an expression.
> >
>
>
>
Received on Thu Feb 28 2002 - 05:26:20 CET

Original text of this message