Sequelize query. Multiple count depends on column name

I have working query:

const count = await models.CompanyProductionUnitNonCeased
    .count({
        distinct: true,
        col: 'company_id',
        include: [{
            required: true,
            model: models.ProductionUnitCore,
            as: "production_unit",
            include: [{
                required: true,
                model: models.ProductionUnitAddress,
                as: 'production_unit_addresses',
                where: { is_current: true },
                include: [{
                    required: true,
                    model: models.AddressAddress,
                    as: 'address',
                    include: [{
                        required: true,
                        model: models.GeograpicalAdministrativeAreas,
                        as: 'geograpical_administrative_areas',
                        include: [{
                            required: true,
                            model: models.Region,
                            as: 'region_code_region',
                            where: {code: [1081, 1082]}
                        }],
                    }]
                }]
            }]
        }]
    })

It returns total count for all columns that has code=1081 and 1082 in “Region” table.

In “Region” table there is “code” row that has 5 different codes. It’s a primary key to GeograpicalAdministrativeAreas table.

I need to calculate for each column separated amount (not only for 1081 and 1082 in total how it is now).

So i want to have the result in this way (or instead of code and we can use row “name”):

[{
  1081: 1001,
  1082: 2002,
  1083: 2222,
  1084: 4344,
  1085: 143434
 }]

Answer

Answer:

const groupedAmount = await models.CompanyProductionUnitNonCeased
.findAndCountAll({
    raw: true,
    distinct: true,
    col: request.aggregate_on === 'cvr' ? 'company_id' : 'production_unit_id',
    attributes: [
        // [sequelize.literal(`DISTINCT company_id`), 'company_id'],
        `production_unit.production_unit_renteds.address.geograpical_administrative_areas.${stringByGroup}.name`,
        `production_unit.production_unit_renteds.address.geograpical_administrative_areas.${stringByGroup}.code`,
        [sequelize.fn('COUNT', `production_unit.production_unit_renteds.address.geograpical_administrative_areas.${stringByGroup}.code`), 'count']
    ],
    group: [
        `production_unit.production_unit_renteds.address.geograpical_administrative_areas.${stringByGroup}.name`,
        `production_unit.production_unit_renteds.address.geograpical_administrative_areas.${stringByGroup}.code`],
    include: [{
        model: models.CompanyCore,
        as: 'company',
        required: true,
        attributes: [],
        where: { id: filters.companyIds }
    }, {
        attributes: [],
        required: true,
        model: models.ProductionUnitCore,
        as: "production_unit",
        include: [{
            attributes: [],
            required: true,
            model: models.ProductionUnitRented,
            as: 'production_unit_renteds',
            where: { is_current: true },
            include: [{
                attributes: [],
                required: true,
                model: models.AddressAddress,
                as: 'address',
                include: [{
                    attributes: [],
                    required: true,
                    model: models.GeograpicalAdministrativeAreas,
                    as: 'geograpical_administrative_areas',
                    include: [{
                        required: true,
                        model: models.Region,
                        as: 'region_code_region',
                        attributes: []
                    }]
                }]
            }]
        }]
    }]
})