How to get count of distinct following GROUP BY in SQL?
<p>Apologies, this is certainly a duplicate but I don't know the right words to google for. </p>

<p>I've got a table of purchasing decisions that looks like this:</p>

<pre><code>org_id    item_id    spend
--------------------------
123        AAB         2
123        AAC         4
124        AAB        10
124        AAD         5
</code></pre>

<p>I want to find all the items that were only bought by three or fewer organisations. Then I want to order them by summed spend. </p>

<p>How would I do this in SQL? NB I'm using <a href="https://cloud.google.com/bigquery/query-reference?hl=en" rel="nofollow">BigQuery SQL</a>. </p>

<p>So far I've got:</p>

<pre><code>SELECT * 
FROM 
  (SELECT ??(org_id) as org_count, -- How do I get the count of different org_ids? 
         item_id, 
         SUM(spend) AS total_spend
  FROM mytable 
  GROUP BY item_id) t
WHERE org_count &lt; 4
ORDER BY total_spend DESC
</code></pre>