SQL: Finding the rarest and most common value in a set of relations
<p>I have a schema that looks like this:</p>

<p>Person</p>

<pre><code>pid     name
</code></pre>

<p>Appearance</p>

<pre><code>pid     latitude    longitude   datetime
</code></pre>

<p>Class</p>

<pre><code>pid     major (1/0)     type
</code></pre>

<p>I'm trying to write a function in Python that finds both the rarest and most common people for each class. My function takes my database name, Collections, as input and I'm trying to get it to return a list with a tuple for each distinct class type and columns with the most and least common person names. This is what I have so far:</p>

<pre><code>def rareCommon(){
    c.execute('SELECT COUNT(mid) AS Most Common FROM Appearance')
}
</code></pre>

<p>I'm having a lot of trouble with the query due its subquery ish nature. If anyone here could help me out, that would be greatly appreciated.</p>

<p>Just a stab in the dark... I agree with @2ps comment - you are a bit light on detail.</p>

<p>The SQL part might look a bit like </p>

<pre><code>SELECT pc.pid,
       MIN(pc.p_count) rare,
       MAX(pc.p_count) most_common
  FROM (SELECT a.pid, 
               COUNT(*) p_count
          FROM appearance a
         GROUP BY a.pid) pc
 GROUP BY pid
</code></pre>

python sql sqlite relational-database schema