Please help in a group by results

Question

3.00/5 (1 vote)

See more:

Hi
I have table like this:

Student Age
A 22
B 28
C 21
D 22
E 4
F 9

I want result like this:

Count Decscription
2 Students below 10 years
3 Students between 10 - 20
4 Student above 20

I can do it already by grouping and doing unioun of three sub quesries.

I sthere a better way?

Posted 22-Aug-13 6:23am

MoneyDude

Add a Solution

Comments

frostcox 22-Aug-13 12:39pm

You could join the table on to itself and get your result from that

RedDk 22-Aug-13 13:13pm

What do the grouping and union of three queries look like?

pradiprenushe 22-Aug-13 13:59pm

create another table with AgeRange(ID,Start,End,Description)
Add data like (1,0,10,'Students below 10 years')
Add data like (2,10,20,'Students between 10 - 20')
Add data like (3,20,100,'Student above 20') then use between clause & group by

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

scottgp · Accepted Answer · 2013-08-22T07:51:00

Your test data didn't look complete (no Students between 10 - 20), so I expanded it, and here's a sample query:

create table test2
(student varchar(10),
  age int)
  
insert into test2 values ('A',22);
insert into test2 values ('B',28);
insert into test2 values ('C',21);
insert into test2 values ('D',22);
insert into test2 values ('E',4);
insert into test2 values ('F',9);

insert into test2 values ('X',11);
insert into test2 values ('Y',12);
insert into test2 values ('Z',13);

select COUNT(*), age_range 
from (
select 
  case 
   when age <10 then '1. Students below 10 years'
   when age between 10 and 20 then '2. Students between 10 - 20'
   when age >20 then '3. Student above 20'
  end age_range
  from test2) v1
  group by age_range
  order by 1

(No column name)	age_range
2	1. Students below 10 years
3	2. Students between 10 - 20
4	3. Student above 20

However, when I had to do something similar recently, I preferred to create a "range" table to join against, in case the range requirements changed:

create table age_range
(rng_sort int,
 rng_desc varchar(25),
 rng_start int,
 rng_end int)
 
 insert into age_range values (1,'Students below 10 years',0,9);
 insert into age_range values (2,'Students between 10 - 20',10,20);
 insert into age_range values (3,'Student above 20',21,999);

 select rng_sort, COUNT(*), rng_desc
  from test2 inner join age_range ar
  on test2.age between rng_start and rng_end
  group by rng_sort,rng_desc
  order by rng_sort

rng_sort	(No column name)	rng_desc
1	2	Students below 10 years
2	3	Students between 10 - 20
3	4	Student above 20

Scott