How to make conditional aggregation using gorm?

Roberto Solares 0
Viewed 28

I want to do a conditional aggregation in order to sum some values of a column into different columns. The data is this:

id amount status
1 100 confirmed
1 50 confirmed
1 50 unconfirmed
1 10 unconfirmed

I want to make it like this:

confirmed unconfirmed total
150 60 210

Currently, I'm able to do this by using raw SQL

SELECT
  sum(amount) FILTER (WHERE status = 'confirmed') AS confirmed,
  sum(amount) FILTER (WHERE status = 'unconfirmed') AS unconfirmed,
  sum(amount) AS total
FROM table_name
WHERE id = 1

How can I make this query using gorm?

orm go-gorm go database postgresql
1 Answers

Is this you expect?


import (
    "fmt"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Res struct {
    Confirmed   int
    Unconfirmed int
    Total       int
}

func main() {
    sql := "select sum(if (status = 'confirmed', amount, 0)) as confirmed ,sum(if (status = 'unconfirmed', amount, 0)) as unconfirmed, sum(amount) as total from m_73627615 where id = 1"
    dsn := fmt.Sprintf("%s:%s@(%s:3306)/%s?charset=utf8&parseTime=True", "", "", "", "")
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    var r Res
    _ = db.Raw(sql).Scan(&r).Error

    fmt.Println(r)
    // {150 60 210}
}
pemako0
Related