Sum of two different columns

I have 3 different tables lilke employees, work_activity, work_category, employess

id,
name,

work_category

id
name
is_base (string and can have two values base or extra) work_activity
id,
employee_id
work_category_id,
work_activity_name,
work_time

I have work_category_id and I want to collect the work_activity.work_time sum in two different sets depending on the work_category_is_base

like I want to get the data set as { employee_name, work_time_for_base_categories work_time_for_extra_categories }

jlrdw

3 years ago

Level 75

Can you give an example here of the expected results. Please not a link. But sounds like you need a GROUP BY.

And there are running sums and final summary sums. Summary sums are separate queries.

Running sums:

Summary sums:

Grouping just example here count used, but sum would be similar:

$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

But not sure on how you need to present the data. Normally some code is shown that has been tried.

I suggest getting a decent visual query builder either MS Access via ODBC or SQLeo is another and build this up to a regular sql query.

1 like

Please or to participate in this conversation.