How to use a calculated value in Oracle SQL count

I have a column called BIRTH_DATE in my view. I want to calculate the age using that column and today’s date, and count the number of staff in an age bracket. For example: staff between 40 and 49 etc.

Other answers in plain SQL statements are also welcome.

My code is shown below:

$count = StaffEmploymentListView::find()
        ->select([
            'COUNT(*) AS TEACHING_COUNT', 
            'BIRTH_DATE' => 'BIRTH_DATE'
        ])
        ->where(['GENDER' => 'MALE', 'JOB_CADRE' => 'ACADEMIC'])
        ->andFilterHaving(['>=', "TRUNC(months_between(sysdate, BIRTH_DATE) / 12)", 40])
        ->andFilterHaving(['<', "TRUNC(months_between(sysdate, BIRTH_DATE) / 12)", 70])
        ->groupBy(['BIRTH_DATE'])
        ->all();

I am calculating the age as shown below:

TRUNC(months_between(sysdate, BIRTH_DATE) / 12)

After doing this if I try to access the TEACHING_COUNT variable in the model it is null.

Answer

You can count the number of records between an age bracket without needing to write any SQL as well.

$from = date('Y-m-d', strtotime('-49 years'));
$to = date('Y-m-d', strtotime('-40 years'));
$query = StaffEmploymentListView::find()
        ->where(['>=', 'BIRTH_DATE', $from])
        ->andWhere(['<=', 'BIRTH_DATE', $to]);

// You can get the count
$count = $query->count();

// Or use the query to display the results
echo Html::tag('h1',
    'Found ' . $query->count() . ' clients born between ' .
    Yii::$app->formatter->asDate($from) . ' and ' . 
    Yii::$app->formatter->asDate($to)
);
$dataProvider = new ActiveDataProvider([
    'query' => $query
]);

echo GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            'name',
            'BIRTH_DATE:date', 
            [
                'attribute' => 'BIRTH_DATE',
                'value' => static function($model) {
                    return floor((time() - strtotime($model->BIRTH_DATE)) / 31556926);
                },
                'label' => 'Age'
            ]
        ]
]);

Since you are already using yii, it seems to make sense to use its methods directly. In this case the count() method.