node.jspostgresqlsequelize-typescript

The issue with aggregation and grouping in Sequelize


My task is to multiply the quantity * price fields, and select the "price", "quantity", "product_id" fields. Also group by date in createdAt. But in this code I get the following error: "error: column "ProductsOfOutgoingInvoice.price" must appear in the GROUP BY clause or be used in an aggregate function". If I add the fields "price", "quantity", "product_id" to the group field, then the error disappears, but the grouping by date disappears. Help me decide to select the fields “price”, “quantity”, “product_id” when sampling, but also to group all requests by date

const productsOfOutgoingInvoice: any = await connection.transaction(
        async (transaction: Transaction) => {
            return ProductsOfOutgoingInvoice.findAll({
                raw: true,
                attributes: [
                    [Sequelize.fn("DATE", Sequelize.col("createdAt")), "date"],
                    [Sequelize.literal("SUM(quantity * price)"), "summ"],
                    "price",
                    "quantity",
                    "product_id"
                ],
                group: [Sequelize.fn("DATE", Sequelize.col("createdAt"))],
                transaction
            });
        }
    );

Error

Error: column "ProductsOfOutgoingInvoice.price" must appear in the GROUP BY clause or be used in an aggregate function

Solution

  • When you select createdAt into the query you change their name by date that why it is not able to group it.