Laravel get count of results based on groupBy

Question

The following is the structure of my database

id  name  country  province   status  updated_at    
1   A     US       TX         2       [timestamp]
2   B     UK       LON        1       [timestamp]
3   C     US       TX         2       [timestamp]
4   D     US       WA         2       [timestamp]
5   E     US       WA         3       [timestamp]
6   F     US       WA         2       [timestamp]
7   G     US       TX         1       [timestamp]

Information from the form (search criteria)

country (US) in this example
Start date, End date

I need to get the following count and information.

Group by province {
- $ca = count(status '1')
- $cb = count(status '2')
- $cc = count(status '3') }

Expected result:: 'US' search criteria:-

Province TX
    Status '1': 1
    Status '2': 2
    Status '3': 0

Province WA
    Status '1': 0
    Status '2': 2
    Status '3': 1

The following is what i trying to do but failed

$users = DB::table('users')
->where('country', $b)
->select('province', DB::raw('count(*) as total'))
->groupBy('province')
->whereBetween('updated_at', [
        \Carbon\Carbon::createFromDate($d, $e)->startOfMonth(),
        \Carbon\Carbon::createFromDate($f, $g)->endOfMonth()
    ])->orderBy('created_at','desc')->get();

score 0 · Answer 1 · edited May 23 '17 at 10:24

Note: My raw MySQL is a little rusty, apologies for any mistakes.

Problem 1: You're grouping by the wrong column(s).

If you want to know the totals of each individual status for each individual province for each individual country, you need to specify all of those items in your GROUP BY clause. As it is, your COUNT will determine the number of unique provinces.

GROUP BY country, province, status

Problem 2: Statuses with no entries will not show up.

Unless you are joining to an outside table for each status, you will not get any counts for non-existent statuses. Given your provided data, the raw results would look more like this:

Country  Province  Status  Total
US       TX        1       1
US       TX        2       2
US       WA        2       2
US       WA        3       1

Here's an example of how to accomplish zero counts for non-existent entries. Using this will totally depend on your data and DB structure.

Potential caveats: ORDER BY

Ordering by the created_at column seems pointless. If you'd like your expected results, you'll want to order specifically by that information:

ORDER BY country, province, status

Solution

I won't include the join to retrieve any zero-result statuses. But here's an untested example of how to translate to Query Builder:

$results = DB::table('users')
    ->select(['country', 'province', 'status', DB::raw('COUNT(*) AS total')])
    ->where('country', '=', $b)
    ->whereBetween('updated_at', [
        \Carbon\Carbon::createFromDate($d, $e)->startOfMonth(),
        \Carbon\Carbon::createFromDate($f, $g)->endOfMonth()
    ])
    ->groupBy('country', 'province', 'status')
    ->orderBy('country') // defaults to ASC
    ->orderBy('province')
    ->orderBy('status')
    ->get();

Alternative Solution

An alternative to a semi-complicated query might be to simply query the set of information you need (entries belonging to $b country, and in between the provided updated_at times), and then process it yourself in PHP.

Laravel's Collection class has handy methods built-in for going over data. These, or even a raw foreach() could get you the information you need. Whatever works best for your situation.

Laravel get count of results based on groupBy

1 Answers1

Linked