Q
Problem solve Get help with specific problems with your technologies, process and projects.

Aggregating date content

I have a table that stores dob (mm/dd/yyyy)in MySQL DB. But now I need to aggregate that table's contents like...

this:

```Group 1: <= 16 years
Group 2: >=17 E <= 25
Group 3: >=26 E <= 40
Group 4: >=41 years
```
MySQL gives the ages, but how can I aggregate them in those groups?

Ideally you would want to use the IF() function to get the aggregates for this table. The IF() function takes three arguments and returns a single value:

```mysql> SELECT IF(1 = 2, 'True', 'False');
+----------------------------+
| IF(1 = 2, 'True', 'False') |
+----------------------------+
| False                      |
+----------------------------+
1 row in set (0.44 sec)
```
As you can see, the first argument is evaluated, and if the first argument evaluates true, the second argument is passed, otherwise the third argument is passed. So how do we use this to aggregate the situation you provided? First we create the aggregates:
```IF(age <= 16, 1,  0)
IF(age >= 17 AND dob <=  25, 1, 0)
IF(age >=  26 AND dob <= 40, 1, 0)
IF(age >= 41, 1, 0)
```
Then we simply provide a sum of the columns and format it as a SELECT query:
```SELECT SUM(IF(age <= 16, 1,  0)) AS VeryYoung,
SUM(IF(age >= 17 AND age <=  25, 1, 0)) AS Young,
SUM(IF(age >=  26 AND age <= 40, 1, 0)) AS GettingThere,
SUM(IF(age >= 41, 1, 0)) AS Others
FROM mytable;
```
Here's a sample of the output you can expect on a table with ages 5, 10, 15...45, 50:
```+-----------+-------+--------------+--------+
| VeryYoung | Young | GettingThere | Others |
+-----------+-------+--------------+--------+
|         3 |     2 |            3 |      2 |
+-----------+-------+--------------+--------+
1 row in set (0.06 sec)
```
And there you have it. As you indicated, you already can determine the age, so we won't go into that in this question.

This was last published in June 2004

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

SearchWindowsServer

Don't get stuck with habits from a time when Microsoft released updates every few years. Admins need to build their proficiency ...

• Microsoft roadmap covers practical and wishful technology

The latest innovations on the Microsoft roadmap include familiar enterprise products, but its wager on an ambitious technology ...

Microsoft said there were no critical vulnerabilities for Windows Server this month, but it issued patches for exploits that ...

SearchServerVirtualization

• Mitigate Windows Server 2016 licensing cost concerns

Microsoft's Windows Server 2016 licensing change could put a serious strain on IT budgets. Though it carries performance risks, ...

• Use libvirt and the libvirtd daemon to manage your hypervisor

Libvirt provides essential functionality that eases hypervisor and VM management in Linux environments. Learn more about the ...

• Choose between hyper-converged servers and conventional

HCI presents a compelling case for admins choosing virtual server systems because of its inexpensive price point, improved ...

SearchCloudComputing

• Google Network Service Tiers grant cloud users more control

For cloud networking, do you prefer higher performance or opt for the lowest possible cost? New Google cloud network tiers let ...

• Fortify a DR strategy with automated failover in multi-cloud

Disasters happen, but they don't have to bring business to a halt. With the right mix of tools and testing strategies, ...