How to do sql count function in laravel 5.2 for join table
<p>i have two table, 'devicelist' and 'devicegroup'. devicelist table contain accountID, groupID, deviceID. devicegroup table contain groupID, description.
In devicelist, groupID means group identification code and deviceID is device name(it is unique), same groupID will repeat for mutiple device .
In devicegroup groupID same as in devicelist, groupID is unique and description is group name. In my view page , need to display groupID, description from 'devicegroup' table and count of device in each groupID from the 'devicelist' table. count of device in each group need to calculate automatically instead of giving one groupID in where clause.</p>

<p>Can any one tell me how to write sql query, what i need to do for getting this output.
Replies are appreciable.
The query i tried is giving below.</p>

<pre><code>    public function getIndex()
    {

$vehicleCount=DB::table('devicelist as dev_list')
    -&gt;select(DB::raw('groupID, dev_group.description, count(*)'))
    -&gt;join('devicegroup as dev_group', 'dev_list.groupID', '=', 'dev_group.groupID')-&gt;get();


echo $vehicleCount;


        $groups = DB::table('devicegroup')-&gt;simplePaginate(10);
        return view('group.groupAdmin')-&gt;with('groups',$groups);
    }
</code></pre>

<p>updated query.</p>

<pre><code>  public function getIndex()
    {

$vehicleCount=$vehicleCount=DB::table('devicelist as a')
    -&gt;join('devicegroup as b', 'a.groupID', '=', 'b.groupID')
    -&gt;select('b.groupID','b.description',DB::raw('count(a.deviceID)'))
    -&gt;where('a.groupID','=','b.groupID')
    -&gt;get();


//echo $vehicleCount;


        //$groups = DB::table('devicegroup')-&gt;simplePaginate(10);
        return view('group.groupAdmin')-&gt;with('vehicleCount',$vehicleCount);
    }
</code></pre>

<p>and my view page is</p>

<pre><code>   @extends('app')

@section('content')
    &lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;
    &lt;div class="templatemo-content-wrapper"&gt;
        &lt;div class="templatemo-content"&gt;
            &lt;ol class="breadcrumb"&gt;
                &lt;li&gt;&lt;a href="{{ url("/") }}"&gt;&lt;font color="green"&gt;Home&lt;/font&gt;&lt;/a&gt;&lt;/li&gt;
                &lt;li class="active"&gt;Group information&lt;/li&gt;
            &lt;/ol&gt;
            &lt;h1&gt;View/Edit Group information&lt;/h1&gt;

            &lt;p&gt;&lt;/p&gt;

            &lt;div class="row"&gt;
                &lt;div class="col-md-12"&gt;
                    &lt;div class="table-responsive"&gt;

                        &lt;table id="example" class="table table-striped table-hover table-bordered"&gt;
                            &lt;h3&gt;Select a Group :&lt;/h3&gt;
                            &lt;thead&gt;
                            &lt;tr&gt;
                                &lt;th&gt;Group ID&lt;/th&gt;
                                &lt;th&gt;Group Name&lt;/th&gt;
                                &lt;th&gt;Vehicle Count&lt;/th&gt;
                                &lt;th&gt;Actions&lt;/th&gt;
                            &lt;/tr&gt;
                            &lt;/thead&gt;
                            &lt;tbody&gt;
                            @foreach($vehicleCount as $grp)
                            &lt;tr&gt;

                            &lt;td&gt;{{ $grp-&gt;groupID }}&lt;/td&gt;
                            &lt;td&gt;{{ $grp-&gt;description }}&lt;/td&gt;

                            &lt;td&gt;{{count($grp)}}&lt;/td&gt;

                                &lt;td&gt;
                                    &lt;div class="btn-group"&gt;
                                        &lt;button type="button" class="btn btn-info"&gt;Action&lt;/button&gt;
                                        &lt;button type="button" class="btn btn-info dropdown-toggle" data-toggle="dropdown"&gt;
                                            &lt;span class="caret"&gt;&lt;/span&gt;
                                            &lt;span class="sr-only"&gt;Toggle Dropdown&lt;/span&gt;
                                        &lt;/button&gt;
                                        &lt;ul class="dropdown-menu" role="menu"&gt;
                                            {{--@if ($nam-&gt;isActive == 'Yes')--}}
                                            &lt;li data-toggle="modal" data-target="#acceptModal" data-bookingid="{{ $grp-&gt;groupID }}"&gt;&lt;a href="{{ url('/group/edit/'.$grp-&gt;groupID) }}"&gt;View/ Edit&lt;/a&gt;
                                            &lt;/li&gt;
                                            {{--@endif--}}
                                            &lt;li&gt;&lt;a href="{{ url('/group/delete/'.$grp-&gt;groupID)}}"&gt;Delete&lt;/a&gt;&lt;/li&gt;
                                        &lt;/ul&gt;
                                    &lt;/div&gt;
                                &lt;/td&gt;
                            &lt;/tr&gt;
                            @endforeach
                            &lt;/tbody&gt;
                        &lt;/table&gt;
                        {{--//{{$groups-&gt;links()}}--}}
                    &lt;/div&gt;
                &lt;/div&gt;
            &lt;/div&gt;
        &lt;/div&gt;
    &lt;/div&gt;
    &lt;/br&gt;
</code></pre>