如何使用GRDB将列求和为双精度

Question

我正在尝试将我的应用程序从SQLite.swift转换到GRDB，但我遇到了一个障碍。我需要对三个单独的列求和，然后用列总数做一些数学运算。这三个列都以实数的形式存储在数据库中，我相信这会使它们加倍。我遇到的问题是，对列求和的三行代码抛出了这个错误。无法将'Double‘类型的值赋给'Double’类型。有没有人能给我解释一下我哪里做错了，怎么改正？提前感谢你的帮助。

func tallyTheResults() {
    
    let theTable = gTheTable
    
    var thePaidTotal = 0.0
    var theShippingTotal = 0.0
    var theSoldTotal = 0.0
    
    // In the Db, Paid, Shipping and SoldFor are stored as Real numbers
    
    do {
        try Database.shared.databaseConnection!.read { db in
            
    // The three lines below are throwing the error in the editor
    // Cannot assign value of type '[Double]' to type 'Double'
    
            thePaidTotal = try Double.fetchAll(db, sql: "SELECT SUM(Paid) FROM " + theTable)
            
            theShippingTotal = try Double.fetchAll(db, sql: "SELECT SUM(Shipping) FROM " + theTable)
            
            theSoldTotal = try Double.fetchAll(db, sql: "SELECT SUM(SoldFor) FROM " + theTable)
        }
        
    } catch {
        print("Fetching Paid, Shipping and SoldFor columns failed: \(error)")
    }
    
    let theTotal = ((Double(theSoldTotal) - (Double(thePaidTotal)) + Double(theShippingTotal)))
    
    paidSum.text = ModelData.convertDoubleToCurrency(amount: thePaidTotal)
    
    shippingSum.text = ModelData.convertDoubleToCurrency(amount: theShippingTotal)
    
    soldSum.text = ModelData.convertDoubleToCurrency(amount: theSoldTotal)
    
    if theTotal < 0
    {
        totalSum.textColor = .red
    } else {
        totalSum.textColor = .black
    }
    
    totalSum.text = ModelData.convertDoubleToCurrency(amount: theTotal)
}


static func convertDoubleToCurrency(amount: Double) -> String {
    let numberFormatter = NumberFormatter()
    numberFormatter.numberStyle = .currency
    numberFormatter.locale = Locale.current
    
    return numberFormatter.string(from: NSNumber(value: amount))!
}

Answer 1

如果希望从单个数据库行中提取一个值，而不是从多个数据库行中提取一组值，则调用fetchOne而不是fetchAll：

thePaidTotal = try Double.fetchOne(db, sql: "SELECT SUM(Paid) FROM " + theTable) ?? 0
theShippingTotal = try Double.fetchOne(db, sql: "SELECT SUM(Shipping) FROM " + theTable) ?? 0
theSoldTotal = try Double.fetchOne(db, sql: "SELECT SUM(SoldFor) FROM " + theTable) ?? 0

当心当表为空时，SUM SQLite函数将返回NULL。然后，fetchOne将返回nil。这就是为什么我在每一行的末尾添加了?? 0，以便将此NULL转换为非可选的0.0双精度值。

有关详细信息，请参阅Fetching Methods。