Forum DB Count on group by.

bweston92

posted 10 years ago

Database

Last updated 2 years ago.

jgarifuna

replied 10 years ago

you may want to look at the count() method from the aggregates section at:

http://laravel.com/docs/queries#aggregates

Last updated 2 years ago.

bweston92

replied 10 years ago

@jgarifuna sorry that doesn't work, that still returns the first result which unfortunately is wrong for example the query above returns:

| COUNT(*) |
| 1          |
| 1          |
| 1          |
| 1          |
| 4          |
| 1          |

Last updated 2 years ago.

bweston92

replied 10 years ago

Solution

I have managed to get this.

select count(*) as `count` from (select
	COUNT(*)
from `mortgages`
inner join `cases` on `mortgages`.`case_id` = `cases`.`id`
inner join `cases_timeline` on `cases_timeline`.`case_id` = `cases`.`id`
where
	`mortgages`.`user_id` in (7)
	and
	`cases_timeline`.`event_id` in (11)
GROUP BY `mortgages`.`id`) sq

Last updated 2 years ago.