Laravel 9中使用WHERE子句的列的SUM

rpppsulh 于 2023-05-01 发布在其他

关注(0)|答案(2)|浏览(135)

我试图在一个查询中使用多个Count和Sum命令。然而，当我使用以下命令时，发生了什么：
SUM(amount AND type = "dispute") as charge
它实际上计算记录的数量，而不是合计数量。我想做的是多次使用SUM和where子句，例如：

`$query = DB::table('cash_table')
        ->where('transaction_created', '>=', Carbon::now()->startOfWeek())
        ->select(DB::raw('
            SUM(amount) as transaction_amount,
            COUNT(amount) as transaction_count,
            SUM(amount) WHERE type = "dispute" as charge_amount
            COUNT(amount) WHERE type = "dispute" as charge_count
            SUM(loan_amount) WHERE type = "loan" as loan_amount
            COUNT(loan_amount) WHERE type = "loan" as loan_count
            '))
        ->groupBy('transaction_created')
        ->get();`

前两个“transaction_amount & transaction count "工作正常，因为没有where子句。

laravel

来源：https://stackoverflow.com/questions/76119283/sum-of-a-column-using-where-clause-in-laravel-9

2条答案

按热度按时间

axkjgtzd1#

试试下面！

$query = DB::table('cash_table')
            ->where('transaction_created', '>=', Carbon::now()->startOfWeek())->select(
                DB::raw('SUM(CASE WHEN type = "dispute" THEN amount ELSE 0 END) as charge'),
                DB::raw('SUM(CASE WHEN type = "refund" THEN amount ELSE 0 END) as refund'),
                DB::raw('COUNT(CASE WHEN status = "approved" THEN 1 ELSE NULL END) as transaction_count'),
                DB::raw('SUM(amount) as transaction_amount')
            )
            ->groupBy('transaction_created')
            ->get();

赞(0）回复(0）举报 2023-05-01

agxfikkp2#

请尝试以下内容：

$query = DB::table('cash_table')
    ->where('transaction_created', '>=', Carbon::now()->startOfWeek())
    ->select(DB::raw('
            SUM(amount) as transaction_amount,
            COUNT(amount) as transaction_count,
            SUM(if(type = "dispute", amount, 0)) as charge_amount
            COUNT(type = "dispute") as charge_count
            SUM(if(type = "loan", amount, 0)) as loan_amount
            COUNT(type = "loan")as loan_count
            '))
    ->groupBy('transaction_created')
    ->get();

赞(0）回复(0）举报 2023-05-01

我来回答

Laravel 9中使用WHERE子句的列的SUM

2条答案

相关问题

热门标签

最新问答