CODEWITHSUNDEEP
mysql
NareN
NareN

Reputation: 11

display age ranges based on mydb using sql query

my db table having two columns name and age .i want to display if age is 21 then agegroup is 20-30,similarly if age is 32 the agegroup is 30-40 in my result display.

i need sql query to display the result like following 

                          Name   age    group      

                          A     22    20-30

                          B     45    40-50

Upvotes: 0

Views: 514

Answers (3)

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7870

In regard to your groups, in the table setup, create a min age column and a max age field in a table that defines your groups.

groups    minAge    maxAge
-------------------------------
20-30    20        30
40-50    40        50

The SQL create statement would look like this:

create table if not exists ageGroups (
  groupName varchar(25),
  minAge INT,
  maxAge INT
  ) ENGINE ndb;

insert into ageGroups (groupName,minAge,maxAge) Values ('20-30',20,30);
insert into ageGroups (groupName,minAge,maxAge) Values ('30-40',30,40);
insert into ageGroups (groupName,minAge,maxAge) Values ('40-50',40,50);


create table if not exists identities (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  age INT,
  PRIMARY KEY (id)
  ) ENGINE nbd;

insert into identities (name,age) values ("A",22);
insert into identities (name,age) values ("B",45);

Then to do your queries you would do search for the where the age value is between the min and max in the ageGroups table:

select i.name,i.age,a.groupName from identities i, ageGroups a where i.age > a.minAge and i.age < a.maxAge;

Output

NAME    AGE    GROUPNAME
A       22     20-30
B       45     40-50

I've made an SQLFiddle here.

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Query

SELECT name, age,
CONCAT(FLOOR(age / 10) * 10,' - ',(FLOOR(age / 10)+1)*10)  AS age_group
FROM tbl;

Find Fiddle Here

Upvotes: 0

Mario
Mario

Reputation: 36487

I'd just try something like this:

SELECT 'name', 'age', FLOOR(age / 10) * 10 AS 'group';

This should get you the following result set:

name | age | group
-----+-----+------
A    | 22  | 20
B    | 45  | 40

You could expand the 'group' column to print a range, but that might complicates things further down the pipe (e.g. when trying to compare or sort), so I'd do that when displaying only.

Upvotes: 0

Related Questions