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

• ### Microsoft hopes an Azure Stack deployment will ease hybrid cloud angst

It's a hybrid cloud world, and Microsoft wants to get in while the getting's good. With its Azure Stack offering, Microsoft wants...

• ### Debug an Active Directory domain join failure on Windows Server

The domain join process typically works without issue. But when a device can't connect to Active Directory, the administrator ...

• ### Reduce downtime with Azure Site Recovery service

Any business can experience data failure. What matters most is how you prepare for a quick recovery. Azure Site Recovery uses the...

## SearchEnterpriseLinux

• ### Is the cloud Linux country?

The forecast calls for Linux to make serious gains in the cloud computing landscape.

• ### OSU Open Source Lab leader looks to further FOSS community outreach

A former Google exec and new Open Source Outreach Manager of Oregon State's Open Source Lab talks building FOSS community ...

• ### Using DavMail to access Exchange’s Outlook Web Access from any POP3/IMAP-compatible mail client

You can connect mail clients other than Outlook to an Exchange server using DavMail, a free open source application. Learn what ...

## SearchServerVirtualization

• ### Best of VMworld 2017 US Awards: Nomination form

Nominate the top virtualization, cloud and end-user computing products for the Best of VMworld 2017 US Awards.

Figure out if load-balancing requirements are dynamic and how aggressively workloads should be balanced. Then look into load ...

• ### Use Hyper-V cmdlets to effectively manage both hosts and VMs

With these PowerShell commands, you can check the Hyper-V replication connection and health status, generate a VM compatibility ...

## SearchCloudComputing

• ### Assess the benefits and risks of compliance as a service

With compliance as a service, organizations can lean more heavily on their cloud providers to ensure adherence with certain ...

• ### Words to go: Google cloud network services

Without a solid network, cloud resources can't communicate like they need to. If you're a Google cloud user, know these options ...

• ### Advance multicloud management with orchestration, cost optimization tools

With the rise of multicloud computing, the focus for cloud admins shifts away from bits and bytes to orchestration, automation ...

Close