can sequelize create union all query like below?

[ruiann]

select SUM(price) as "SUM(price)", type, 'a' as "sub"
from sale_record
where price between 200 and 300 and profit between 0 and 100
group by type
union all
select SUM(price) as "SUM(price)", type, 'b' as "sub"
from sale_record
where price between 300 and 400 and profit between 100 and 200
group by type
union all
select SUM(price) as "SUM(price)", type, 'c' as "sub"
from sale_record
where price between 300 and 400 and profit between 200 and 300
group by type

I've found groupedLimit property in findAll api, but no detail information for it. After looked test cases, I found it only support only one field in groupedLimit.on in test cases.

[sushantdhiman]

You can generate these statements using selectQuery then join them with union all

var query = sequelize.dialect.QueryGenerator.selectQuery('sale_record', {
    attributes: [
      sequelize.fn('SUM', sequelize.col('price')),
      'type',
      ['a', 'sub']
    ],
    where: {
      price: {
        [Op.between]: [200 , 300]
      },
      profit: {
        [Op.between]: [0, 300]
      }
    },
    group: ['type']
  });

SELECT SUM("price"), "type", "a" AS "sub" FROM "sale_record" WHERE "sale_record"."price" BETWEEN 200 AND 300 AND "sale_record"."profit" BETWEEN 0 AND 300 GROUP BY "type";

Beyond that there is no support for generating unions