Laravelでカウント・合計値・平均値などを実装する方法

2023 10/28

2021年12月18日 2023年10月28日

Laravelでレコードのカウントや合計値、平均値を計算したい場合はどうすればいいんだろう…

こんな疑問を解決します。

結論、COUNT、SUM、AVG関数を使いましょう。

今回使用するテーブルはbooksテーブルで、以下のようなデータが入っています。

booksテーブル

>>ココナラと似てるおすすめの副業サイトを確認する

あわせて読みたい

ココナラと似てるサイト比較11選！【2023年最新版】この記事ではココナラに似てる副業サイトを11こ紹介しています。今すぐスキルをお金にするなら、記事で気になったサービスに登録してみましょう。本記事のゴールココ…

>>リモートワークもあるおすすめの転職サイトを確認する

あわせて読みたい

転職におすすめなサイト11選【リモート・フリーランスも対象】転職におすすめなサイトが知りたいなぁ… 転職エージェントによって相性があります！この記事では、フリーランスの方でも利用できる転職におすすめなサイトを紹介しま…

休日で空いた時間の暇つぶしを探せるアプリを公開しています。

yoteipickerで休日の暇つぶしを探す

Contents

COUNTでレコードをカウントする

レコードの数を集計したい！って時はCOUNTを使います。

実行結果として、book_idのレコードがいくつあるか集計できますよ！

book_idのレコード数は、6なのでこれを求めるにはどうすればいいのか以下解説します。

SQL

SELECT
COUNT(book_id) as count_book
FROM
books;

// book_idのカウント
$books = Book::selectRaw('COUNT(book_id) as count_book')
         ->get();

selectRawは、select(DB::raw())の省略形です。COUNTはSQLの関数になるので、select(COUNT)とは使えません。使うには生のSQLが使えるDB::raw(‘COUNT()’)と書く必要があることに注意。

<table class="table table-striped book-list-table">
  <thead>
    <tr>
      <th>ブック数</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($books as $book)
    <tr>
      <td>{{ $book->count_book }}</td>
    </tr>
    @endforeach
  </tbody>
</table>

レコード数が6と求まりました。

ユーザーごとにグループ化してブック数をカウント

レコード数をユーザーごとに集計したい場合もあるでしょう。

例えば、以下のようにuser_id=4の人はいくつ、user_id=1の人はいくつみたいな。

その場合は、グループ化させて集計します。

SELECT
user_id,
COUNT(book_id) as count_book
FROM
books
GROUP BY
user_id;

// ユーザーごとにグループ化してbook_idのカウント
$books = Book::select('user_id')
         ->selectRaw('COUNT(book_id) as count_book')
         ->groupBy('user_id')
         ->get();

<table class="table table-striped book-list-table">
  <thead>
    <tr>
      <th>ユーザー</th>
      <th>ブック数</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($books as $book)
    <tr>
      <td>{{ $book->user_id }}</td>
      <td>{{ $book->count_book }}</td>
    </tr>
    @endforeach
  </tbody>
</table>

以下のようにユーザーごとにグループ化してカウントできました。

重複するレコードをカウントする場合

重複するレコードをカウントする場合は、COUNT関数とDISTINCTを併用します。

そもそもDISTINCTはSQLの知識なので詳細は、SQL DISTINCTで検索してもらえればと思うのですが、要は重複レコードを1つにまとめる機能があります。

例えば、カテゴリーとサブカテゴリーテーブルをカテゴリーIDで外部結合し、カテゴリーテーブルのカテゴリーIDがいくつあるかカウントする場合です。

想定する結果は、カテゴリーIDの数は1ですが、ただ単に外部結合してCOUNTをすると、3になります。

重複してカウントしてしまっているのが原因なので以下のようなSQLで解決しましょう。

SQL

SELECT
COUNT(DISTINCT categories.category_id)
FROM
categories
LEFT JOIN
sub_categories
ON
categories.category_id = sub_categories.category_id
WHERE
sub_categories.category_id = 1

クエリビルダで表すとこんな感じ。

$categories = Category::selectRaw('COUNT(DISTINCT category_id) as count_category')
              ->join('categories', function($join) {
              　　$join->on('categories.category_id', 'sub_category.category_id');
              })
              ->where(sub_categories.category_id, 1)
              ->get();

SUMで合計値を計算

book_max_priceの合計値を求めたい場合は、SUM関数を使います。

SELECT
SUM(book_max_price) as max_price
FROM
books

// book_max_priceの合計値
$books = Book::selectRaw('SUM(book_max_price) as max_price')
         ->get();

<table class="table table-striped book-list-table">
  <thead>
    <tr>
      <th>最大合計値</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($books as $book)
    <tr>
      <td>{{ $book->max_price }}</td>
    </tr>
    @endforeach
  </tbody>
</table>

以下のように合計値が求まります。

ユーザーごとにグループ化して合計値を計算する

全体の集計ではなく、ユーザーごとに集計したい場合は、ユーザーをグループ化して集計します。

以下では、user_id=4のbook_max_priceの合計、user_id=1のbook_max_priceの合計を求めていきます。

SELECT
user_id,
SUM(book_max_price) as max_price
FROM
books
GROUP BY
user_id;

// book_max_priceをユーザーごとにグループ化して合計値を計算
$books = Book::select('user_id')
         ->selectRaw('SUM(book_max_price) as max_price')
         ->groupBy('user_id')
         ->get();

<table class="table table-striped book-list-table">
  <thead>
    <tr>
      <th>ユーザー</th>
      <th>最大合計値</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($books as $book)
    <tr>
      <td>{{ $book->user_id }}</td>
      <td>{{ $book->max_price }}</td>
    </tr>
    @endforeach
  </tbody>
</table>

以下のようにユーザーごとにグループ化させて合計を出せました。

AVGで平均値を求める

平均値を求めたい場合は、AVG関数を使います。

以下の例で言うと、book_max_priceの平均を求めていきましょう。
※ユーザーごとにグループ化して平均値を出していきます。

SELECT
user_id,
AVG(book_max_price) as max_price
FROM
books
GROUP BY
user_id;

// ユーザーごとにグループ化して平均値を計算
$books = Book::select('user_id')
         ->selectRaw('AVG(book_max_price) as max_price')
         ->groupBy('user_id')
         ->get();

<table class="table table-striped book-list-table">
  <thead>
    <tr>
      <th>ユーザー</th>
      <th>平均値</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($books as $book)
    <tr>
      <td>{{ $book->user_id }}</td>
      <td>{{ $book->max_price }}</td>
    </tr>
    @endforeach
  </tbody>
</table>

以下のようにユーザーごとにグループ化させてbook_max_priceの平均値を出せました。