title: How to create an additional column in a SQL query that contains the number of rows with a column value equal to a column value from the current row?
body: <p>This is what I currently have (it doesn't work):</p>
  <pre><code>select MOCKSTEMS.WORD_ID,
         MOCKSTEMS.STEM_ID,
         MOCKSTEMS.LABSTEM,
         MOCKSTEMS.LABSTEMCATEGORY,
         MOCKLEMMAS.LEMMAFORM,
         MOCKSTEMS.LEMMA_ID,
         MOCKWORDS.ORIGINALWORD,
         MOCKSTEMS.CONTAINEDIN,
         COUNT(*) as SAMEVALUE from MOCKSTEMS where CONTAINEDIN=STEM_ID
  from MOCKSTEMS
  inner join MOCKWORDS on MOCKSTEMS.WORD_ID = MOCKWORDS.WORD_ID
  inner join MOCKLEMMAS on MOCKSTEMS.LEMMA_ID = MOCKLEMMAS.LEMMA_ID
  </code></pre>
  <p>Basically, I wish to create a column called 'SAMEVALUE' that shows the number of rows in this query with 'CONTAINEDIN' values equal to the 'STEM_ID' value of each row. Is this possible, and if so, how can I do it with SQL?</p>
  <p>EDITED:</p>
  <p>This is what I get when I run the query without the 'COUNT(*) as SAMEVALUE from MOCKSTEMS where CONTAINEDIN=STEM_ID' row:
  <a href="https://i.stack.imgur.com/IQFDM.png" rel="nofollow noreferrer">image of a few rows returned by the query</a>.</p>
  <p>For example, for the row with STEM_ID='stem-003' and LABSTEM='owotan okitz', I would like the SAMEVALUE column to have value 2, because there are 2 rows with CONTAINEDIN='stem-003', as <a href="https://i.stack.imgur.com/iGkke.png" rel="nofollow noreferrer">circled in this image</a>.</p>
  <p>It would also be fine if the SAMEVALUE column just indicates true/false (or 0/1) depending on whether there are rows with CONTAINEDIN values equal to the STEM_ID of each row.</p>