How to get count of records by month using node js sequelize and postgresql

Viewed 451

I need count of records in each month using Node Sequelize and PostgreSQL. I checked with following code :

User.findAll({
    attributes: [[sequelize.fn('DATE_TRUNC', 'month', sequelize.col('createdAt')), 'month'], [sequelize.fn('COUNT', 'id'), 'totalCount']],
    where: queryCondSend,
    group: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))]
})

But the output is like

[
    {
        "month": "2021-02-01T00:00:00.000Z",
        "totalCount": "4"
    }
]

The count is displayed only for months in the table. I need a count 0 for months which don't have any records. like below:

[
    {
        "month": 01,
        "totalCount": 0
    },
    {
        "month": 02,
        "totalCount": 10
    },
        .
        .
        .

]

Please help. Thank you.

0 Answers
Related