Sequelize get data of another column while using Aggregate Function

Question

I'm trying to get data using Sequelize in my Express app and I'm using SQL Server as database.

Here is my code:

Order.findAll({
    attributes:[
        [Sequelize.fn('SUM', Sequelize.col('total_price')), 'total_price'],
    ],
    include:[
        {model: Customer, attributes:['name']}
    ],
    group:['Customer.id','Customer.name']
}).then(result =>res.json(result))

This code is running well, and successfully returns the data with aggregate function. But when I did added another attribute like 'product_id', 'status', 'expired_date' that doesn't need aggregation, it returns me an error like

Column 'Customer.product_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Based on this article, it only can get columns which are in the GROUP BY list.

But how is the best way get the added attribute and the aggregation result? Like:

{
     'total_price': 2000,
     'name': 'Jack',
     'status': 'Paid'
}

Should do 2 get data in a function, like first I get the aggregate, and then get another data. I'm worried there are possibilities return unrelated data.

Blackjack · Accepted Answer · 2019-08-27 20:08:27Z

3

I just found a way to do that, I only added the attribute/column that I want to get into the group_by clause. Just like this:

Order.findAll({
attributes:[
    'product_id', 'status', 'expired_date',
    [Sequelize.fn('SUM', Sequelize.col('total_price')), 'total_price'],
],
include:[
    {model: Customer, attributes:['name']}
],
group:['Customer.id','Customer.name','Order.product_id', 'Order.status', 'Order.expired_date']
}).then(result =>res.json(result))

It's successfully returns my expected result. But I don't know is there any consideration if do it like this? Thanks

answered Aug 27, 2019 at 20:08

Blackjack

1,0752 gold badges24 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Md Alamin Over a year ago

How do I get these columns that I was not grouped??

Collectives™ on Stack Overflow

Sequelize get data of another column while using Aggregate Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related