i have this table and i want to seperate it into multiple column of date and SUM the score on that date using Query Builder laravel or Raw SQL so it become like :

all of this is upto the current month so january until 31 and so on

CodePudding user response：

You can do this using Laravel Query Builder.

$results = DB::table('my_table')
    ->select('Name', DB::raw("SUM(CASE WHEN Date = '2023-01-01' THEN Score ELSE 0 END) AS Day_1"), 
                     DB::raw("SUM(CASE WHEN Date = '2023-01-02' THEN Score ELSE 0 END) AS Day_2"), 
                     DB::raw("SUM(CASE WHEN Date = '2023-01-03' THEN Score ELSE 0 END) AS Day_3"))
    ->groupBy('Name')
    ->get();

CodePudding user response：

You aren't providing anything like your attempted query, how you are passing the date ( it is a range, month only etc ), and your desired json ouput.

its hard to even assume how you are going to do things specially you are passing a column value as column name in your desired result (which doesn't make much sense with raw sql query unless those columns aren't dynamic).

but to give you a starting point, you can simply group them by name, then date, then do another grouping by date in the collection

e.i;

$result = DB::table('table_name')->select([
    'name',
    'date',
])
->selectRaw('sum(score) AS score')
->groupBy(['name', 'date'])->get();

return $result->groupBy('date');

then you should be able to get result in a format like below;

{
    "01-01-2023" : [
        {
            "name": "A",
            "date": "01-01-2023",
            "score": "300"
        }
    ],
    "02-01-2023" : [
        {
            "name": "A",
            "date": "02-01-2023",
            "score": "300"
        }
        {
            "name": "B",
            "date": "02-01-2023",
            "score": "200"
        }
    ],
    "03-01-2023" : [
        .
        .
        .
    ]
}

For you desired table result, thats better be changed to a dynamic rows instead of dynamic column

EDIT

In reference with Karl answer, you can loop through a date range and inject additional select statement.

e.i. current month dates

$dateRange = \Carbon\CarbonPeriod::create(now()->startOfMonth(), now()->endOfMonth() )->toArray();

$result = DB::table('table_name')->select(['name']);

foreach ($dateRange as $date) {
    $dateFormat = $date->format('d-m-Y');
    $day = $date->format('j');
    $result->selectRaw("SUM(CASE WHEN Date = '$dateFormat' THEN Score ELSE 0 END) AS 'Day $day'");
}

return $result->groupBy('name')->get();