How to use (COUNT) in a database query

I am trying to replicate this postgres query:

select category, COUNT(category) from auctionschedule  where scheduled_for >= now() group by (category);

I was attempting this, but I get an error "Call to a member function groupBy() on int"

        $date = new \DateTime();
        $date->getTimestamp();

        $number_of_auctions_of_each_category = Auctionschedule::select('category')
            ->where('scheduled_for', '>=', $date)
            ->count('category')
            ->groupBy('category')
            ->get();

If I comment out "->count('category')" then the query completes but there is no summary of the category.

What am I missing?

jlrdw

5 years ago

Best Answer

Level 75

Just example:

$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

count is this line:

->selectRaw('count(dc_pets.petid) as countOfPets')

scottsuhy

5 years ago

Level 1

you rock.

        $number_of_auctions_of_each_category = Auctionschedule::select('category')
            ->selectRaw('count(category)')
            ->where('scheduled_for', '>=', $date)
            ->groupBy('category')
            ->get();

i didn't want to do it like this but i guess it would work too:

$number_of_auctions_of_each_category = DB::table('auctionschedule')->select(DB::raw('category, COUNT(category)'))
            ->where('scheduled_for', '>=', $date)
            ->groupBy('category')
            ->get();

jlrdw

5 years ago

Level 75

Thanks and best of luck with your project.

Please or to participate in this conversation.