SUM row field MEDOO

Question

I am trying to get a summed field in medoo.

My sql at the moment is like:

$database->debug()->select("user_rupees_in_house", [
    "[<]rupees" => ["rupee_id" => "ID"]
], [
    "name",
    "amount",
    "amount_spend"
], [
    "user_uuid"  => $user,
    "GROUP" => "rupee_id"
]);

The debug logs the following statement:

SELECT `name`,`amount`,`amount_spend`, `rupee_id`
FROM `user_rupees_in_house` 
RIGHT JOIN `rupees` 
ON `user_rupees_in_house`.`rupee_id` = `rupees`.`ID` 
WHERE `user_uuid` = '4da9ff11-56ca3a2f-b3ab-a25b9230'
GROUP BY `rupee_id`

What I'm trying to achieve is:

SELECT `name`,SUM(`amount`),SUM(`amount_spend`), `rupee_id`
FROM `user_rupees_in_house` 
RIGHT JOIN `rupees` 
ON `user_rupees_in_house`.`rupee_id` = `rupees`.`ID` 
WHERE `user_uuid` = '4da9ff11-56ca3a2f-b3ab-a25b9230'
GROUP BY `rupee_id`

Does anybody know how to make this statement in medoo?

[EDIT 1]

Found another way of achieving this

// Get the rupee types
$rupee_types = $database->select("rupees", "ID");

foreach ($rupee_types as $rupee_type) {
    $amount = $database->sum("user_rupees_in_house", "amount", [
        "AND" => [
            "rupee_id" => $rupee_type,
            "user_uuid" => $user
        ]
    ]);

    // Build array of rupees
}

This will make a lot more calls to the database, but is working just fine as long as the SELECT statement does not support aggregate functions.

Samir Selia · Accepted Answer · 2016-02-27T13:32:08.653

1

Medoo doesn't support aggregate function in SELECT statement. Use raw query instead.

Try this:

$result = $database->query(
    "SELECT `name`,SUM(`amount`),SUM(`amount_spend`), `rupee_id`
    FROM `user_rupees_in_house` 
    RIGHT JOIN `rupees` 
    ON `user_rupees_in_house`.`rupee_id` = `rupees`.`ID` 
    WHERE `user_uuid` = '$user'
    GROUP BY `rupee_id`"
)->fetchAll();

Reference

edited Feb 27 '16 at 13:32

answered Feb 27 '16 at 12:32

Samir Selia

7,007
2
11
30

This will result in: SELECT "name","" AS "amount","" AS "amount_spend" FROM "user_rupees_in_house" RIGHT JOIN "rupees" ON "user_rupees_in_house"."rupee_id" = "rupees"."ID" WHERE "user_uuid" = '4da9ff11-56ca3a2f-b3ab-a25b9230' GROUP BY "rupee_id" – Xiduzo Feb 27 '16 at 13:00
Sorry. It's my mistake. `Medoo` doesn't support aggregate function in `select` statement. You can use raw query instead. will Rectify my post. – Samir Selia Feb 27 '16 at 13:05
this works, only needed to add '->fetchAll()' after the statement. But there must be a better way with using the medoo class (?) – Xiduzo Feb 27 '16 at 13:24
Current version doesn't support aggregate functions in `SELECT` statement. But you can use `sum` method as shown in reference link in my post. – Samir Selia Feb 27 '16 at 13:30
Prever to make just one statment, but thanks for your help :D – Xiduzo Feb 27 '16 at 13:39

SUM row field MEDOO

1 Answers1