Aggregate queries in Laravel Query Builder with group by

Question

I have a table birds.

I want to count each kind of birds in a single query.

I want to combine these queries into a single query in Eloquent if possible.

 select count(id) as count1 from birds where kind = a;

 select count(id) as count2 from birds where kind = b;

 select count(id) as count2 from birds where kind = c;

I tried something like

$first = DB::table('birds')->selectRaw('count(id) as count1')->where('kind','a');
DB::table('birds')->selectRaw('count(id) as count2')->where('kind','b')->unionAll($first)->get();

i don't think union is giving me what i want.

i just need something like

DB::raw(' (select count(id) from birds where kind = a) as count1 ', ' (select count(id)  from  birds where kind = a) as count2  ', ' (select count(id) from birds where kind = a) as count3 ')

i want to combine the queries

like

Select ( select count(id)  from birds where kind = 'a') as count1, ( select count(id)  from birds where kind = 'b') as count2,  ( select count(id)  from birds where kind ='c') as count3 from birds ;

. please give me a tip how to achieve it.

I've rolled back your question to the one that you have accepted an answer to. This will make it more useful to future visitors. — Nick
– Nick, Commented Oct 30, 2020 at 2:40

Jarek Tkaczyk · Accepted Answer · 2015-01-08 14:24:00Z

5

Learn SQL, group by and aggregates in particular.

This is what you need in Laravel:

DB::table('birds')
  ->selectRaw('count(id) as count, kind')
  ->groupBy('kind')
  ->lists('count', 'kind');
  // or get()

lists will return an array like below:

array(
  'kind_1' => '15',
  'kind_2' => '10',
  ...
);

get would return an array of stdObjects so probably not what you would like:

array(
  0 => StdObject {
    'kind' => 'kind_1',
    'count' => '15'
  },
  1 => StdObject {
    'kind' => 'kind_2',
    'count' => '10'
  },
  ...
);

If you want to get only particular kinds of birds, then use whereIn:

DB::table('birds')
  ->selectRaw('count(id) as count, kind')
  ->groupBy('kind')
  ->whereIn('kind', ['kind_1', 'kind_2', 'kind_3'])
  ->lists('count', 'kind');

edited Jan 8, 2015 at 14:24

answered Jan 8, 2015 at 13:42

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Laps Sti Over a year ago

Hi thanks for your reply. I should have used another example didn't see that coming. How do you do it if instead of kind i will query date_added. like select count(id) as count1 from birds where date added = today/yesterday/3monthsago;

Jarek Tkaczyk Over a year ago

you want groupped results or just when where condition is matched? In fact it doesn't matter, because you still want group by, definitely no union or whatever you tried. Unless there are separate tables or anything alike.

Laps Sti Over a year ago

where condition is matched. like how to combine these three: select count(id) as count1 from birds where date_added = today; select count(id) as count2 from birds where date_added = 2012-10-11; select count(id) as count2 from birds where kind = 2012-10-9;

Laps Sti Over a year ago

I mean. I need a count for each based on the date i added.

Laps Sti Over a year ago

what i'm trying to achive is combine like 30 small queries to one. select count(x) from table1 where date added = monday, select count(x) from table1 where date added = tuesday, select count(x) from table1 where date added = wednesday,

|

Collectives™ on Stack Overflow

Aggregate queries in Laravel Query Builder with group by

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related