Group the category and get count from period date

Hello, and happy new years.

| ID | category | status | created_at | updated_at | |----|-------------|-------------|---------------------|---------------------| | 1 | Server | Complete | 2019-12-23 13:15:12 | 2019-12-23 13:12:57 | | 2 | Laptop | In Progress | 2019-12-23 13:15:12 | 2019-12-23 13:12:57 | | 3 | Desktop | Complete | 2019-12-23 13:15:12 | 2019-12-23 18:11:29 | | 4 | Desktop | In Progress | 2019-12-23 13:15:12 | 2019-12-23 18:11:29 | | 5 | Maintenance | In Progress | 2019-12-23 13:15:12 | 2019-12-23 18:11:29 | | 6 | Server | Complete | 2019-12-23 13:15:12 | 2019-12-23 18:11:29 | | 7 | Laptop | Complete | 2019-12-23 13:15:12 | 2019-12-23 18:11:29 |

Any idea how to group the category where its status are complete and count the between created_at and updated_at if its below 2 day and more than 2 days.

the result suppose be right here

| Category | Less than 2 days | More than 2 days | Average Time | |----------|:----------------:|-----------------:|---------------------| | Server | 2 | 6 | 6 Hours 10 Minute | | Laptop | 1 | 4 | 12 Hours 3 Minutes | | Desktop | 4 | 10 | 4 Hours 32 Minutes | | TOTAL | 7 | 20 | 22 Hours 45 Minutes |

fylzero

6 years ago

Level 67

@faezx3 I would tend to create a categories table and just store a look up I'd for the category. Then you could just get the categories and loop those to obtain each count. There is probably a way to do what you want in eloquent, but that is the way I would structure this.

24 likes

jlrdw

6 years ago

Level 75

You can do an eloquent query which goes something like this, not your data but just example:

Also I just gave a quick answer a while back to someone looking for join and group by together.

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1
// more

Please or to participate in this conversation.