<p>Not sure if this is the best way to do it, but I created this little function in a Variable class.  I get all the necessary info (username and score) in an array.  Works fine for what I needed.</p>

<pre><code>public function leaderboard(ElementCriteriaModel $criteria)
   {
       $query = craft()-&gt;elements-&gt;buildElementsQuery($criteria);

   if ($query)
   {
      $query
          -&gt;select("authorId, SUM(field_score) as score, users.username as username")
          -&gt;join("users", "users.id = authorId" )
          -&gt;group("authorId")
          -&gt;order("score DESC");

               return $query-&gt;queryAll();
       }
   }
</code></pre>

CC BY-SA 3.0
2017-08-01T19:28:06.637Z
2017-08-01T19:28:06.637Z
Eric Chantigny
<p>I am trying to build a leaderboard page, and I'm having difficulties finding a way to group the entries by Author.</p>

<p>I was able to create a plugin to get the sql sum function in there, but I now need to add a group by Author and order by Score DESC to that.</p>

<p>First steps were taken from here: <a href="https://craftcms.stackexchange.com/questions/1687/ability-to-sum-or-group-by-from-the-template-or-elementcriteriamodel">Ability to SUM() or GROUP BY from the template or ElementCriteriaModel</a></p>

<p>What I ultimately want is a query that would look like this (very simplified of course):</p>

<pre><code>SELECT author, SUM(content.score) as score FROM content Group By Author Order By score DESC
</code></pre>

<p>Is there a way to do this "natively" or do I need to write my own query?</p>

<plugin-development><database><query>
Sum with a group by Author
# Sum with a group by Author

(Asked by: username_0 on 2017-08-01)

I am trying to build a leaderboard page, and I'm having difficulties finding a way to group the entries by Author.
I was able to create a plugin to get the sql sum function in there, but I now need to add a group by Author and order by Score DESC to that.
First steps were taken from here: Ability to SUM() or GROUP BY from the template or ElementCriteriaModel
What I ultimately want is a query that would look like this (very simplified of course):
SELECT author, SUM(content.score) as score FROM content Group By Author Order By score DESC

Is there a way to do this "natively" or do I need to write my own query?

**Tags:** plugin-development, database, query


---

## 1 answers

### Answer by username_0 on 2017-08-01. Score: 2 | Accepted answer

Not sure if this is the best way to do it, but I created this little function in a Variable class.  I get all the necessary info (username and score) in an array.  Works fine for what I needed.
public function leaderboard(ElementCriteriaModel $criteria)
   {
       $query = craft()->elements->buildElementsQuery($criteria);

   if ($query)
   {
      $query
          ->select("authorId, SUM(field_score) as score, users.username as username")
          ->join("users", "users.id = authorId" )
          ->group("authorId")
          ->order("score DESC");

               return $query->queryAll();
       }
   }


---

