I have two table that I want to union and after I union them I want to groupBy the one column that I used in union
Here is what I tried:
$issuance = DB::table('issuance as i')
->select('i.issue_to as stud_id', 'i.created_on');
$stud= DB::table('transfer as t')
->select('t.transfer_to as stud_id', 't.created_on')
->union($issuance)
->select('stud_id', 'created_on', DB::raw('COUNT(*) as total_asset'))
->groupBy('stud_id')
->orderBy('created_on', 'DESC')->get();
This is the MySQL query in what I tried
"(select `stud_id`, `created_on`, COUNT(*) as total_asset from `transfer` as
`t` group by `stud_id`) union (select `i`.`issued_to` as `stud_id`, `i`.`created_on` from
`issuance` as `i`) order by `created_on` desc"
What I really want in MySQL is like this:
select stud_id, count(*) from ((select `t`.`transfered_to` as `stud_id`,
`t`.`created_on` from `transfer` as `t`) union (select `i`.`issued_to` as
`stud_id`, `i`.`created_on`, COUNT(*) as asset from `issuance` as `i`)) as t
group by stud_id order by `created_on` desc
Thank you for the help
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire