Laravel: Get week sales year by year

I am trying to get all weeks of year sales year by year

I need the output like:

|Sales | Wk1 | Wk2 | Wk3 | Wk4 | Wk5 | Wk6 | Wk7 | Wk8 | Wk9 | Wkx | Wkx | Wkx | Total ($) | | 2023 | 0 | 0 | 11 | 5 | 6 | 4 | 0 | 2 | 8 | 8 | 20 | 6 | 70 | | 2024 | 10 | 5 | 5 | 10 | 0 | 6 | 2 | 0 | 9 | 5 | 8 | 3 | 59 | | 2025 | 6 | 0 | 0 | 2 | 6 | 0 | 0 | 10 | 8 | 15 | 7 | 11 | 65 |

The query I have so far, how to I group the sales by week first, then only group by current year and past 3 year. Also how to I get the total sales for each year?

$sales = Sales::select( DB::raw('year(created_at) as year'), DB::raw('week(created_at) as week'), DB::raw('sum(price) as price'), ) ->whereBetween('created_at', [$request->startDate, $request->endDate]) // filter sales by date ->where(DB::raw('date(created_at)'), '>=', "2022-01-01") ->groupBy('year') ->groupBy('week') ->get() ->toArray(); return $sales;

jlrdw

1 year ago

Level 75

There is https://laracasts.com/discuss/channels/eloquent/group-by-week

But quite a few other good past answers on grouping by week.

Also can you follow this:

https://docs.github.com/en/get-started/writing-on-github/getting-started-with-writing-and-formatting-on-github/basic-writing-and-formatting-syntax

And format your code.

tisuchi

1 year ago

Level 70

@malikoo you can try this:


$sales = Sales::selectRaw('YEAR(created_at) as year, WEEK(created_at, 1) as week, SUM(price) as total')
    ->whereBetween('created_at', [$request->startDate, $request->endDate])
    ->whereDate('created_at', '>=', '2022-01-01')
    ->groupBy('year', 'week')
    ->orderBy('year')
    ->orderBy('week')
    ->get();

Please or to participate in this conversation.