How to get a distinct count with sequelize?

When using Sequelize for database operations, obtaining distinct counts is a common requirement, especially when handling large datasets and requiring statistical information. Below, I will detail how to use Sequelize to obtain distinct counts of various types, including examples of simple counts, conditional counts, and grouped counts.

1. Simple Count

If you simply want to count the number of rows in a table, you can use the count method. This is the most basic counting approach.

javascript
const User = require('../models/user');

async function countUsers() {
  try {
    const count = await User.count();
    console.log(`Total users: ${count}`);
    return count;
  } catch (error) {
    console.error('Error:', error);
  }
}

2. Conditional Count

When you want to count the number of records that meet specific conditions, you can pass the conditions as parameters to the count method.

javascript
async function countActiveUsers() {
  try {
    const count = await User.count({
      where: {
        isActive: true
      }
    });
    console.log(`Total active users: ${count}`);
    return count;
  } catch (error) {
    console.error('Error:', error);
  }
}

In this example, we count the number of users where isActive is true.

3. Grouped Count

When you need to group by certain fields and calculate the count for each group, you can use the group option.

javascript
const { Sequelize } = require('sequelize');

async function countUsersByCountry() {
    try {
        const count = await User.count({
            group: 'country',
            attributes: ['country', [Sequelize.fn('COUNT', Sequelize.col('id')), 'count']],
        });
        console.log('User counts by country:', count);
        return count;
    } catch (error) {
        console.error('Error:', error);
    }
}

This example demonstrates how to group by the country field and calculate the number of users per country.

Summary

Through the above examples, you can see that using Sequelize to obtain distinct counts of various types is straightforward and flexible. By selecting the appropriate method based on specific requirements, you can effectively extract statistical data from the database. In practical applications, adjust query conditions and parameters based on business requirements to achieve the desired statistical results.