title: MySQL How To Use Main Select Value As Subquery Argument?
body: <p>I am trying to construct a query that is a bit more complicated than anything I've done in my limited experience with databases.</p>
  
  <pre><code>TABLE:
  
  id - data - type - data3
  1 - hello - 1 - 1
  2 - goodbye - 1 - 1
  3 - goodbye - 1 - 2
  4 - goodbye - 2 - 1
  5 - hello - 2 - 1
  </code></pre>
  
  <p>The goal is to do 4 things: </p>
  
  <ol>
  <li>GROUP the results by "data", but only return one result/row of each
  data type.</li>
  <li>COUNT the total number of each "data GROUP and return this number.</li>
  <li>Do this for both "type"=1 and "type"=2, though I only need each
  "data" GROUP item once.</li>
  <li>the ability to sort results based on each SELECT item.</li>
  </ol>
  
  <p>So the final result returned should be (sorry to be confusing!):</p>
  
  <pre><code>data, COUNT(data["type"]=1), COUNT(data["type"]=2 AND data["data"] = data)
  </code></pre>
  
  <p>So, for the sample table above, desired results would be:</p>
  
  <pre><code>loop 1 - hello, 2, 1
  loop 2 - goodbye, 3, 1
  </code></pre>
  
  <p>Then, ideally, I could sort results by any of these.</p>
  
  <p>This is the query I was trying to construct before resorting to posting this, I don't think it's even close to being correct, but it may help illustrate what I'm trying to achieve a bit better:</p>
  
  <pre><code>SELECT 
    (
     SELECT `clicks_network_subid_data`, COUNT(*) 
     FROM track_clicks 
     WHERE `clicks_campaign_id`='$id' AND `clicks_click_type` = '1'
    ) AS keywords, 
    (
     SELECT COUNT(*) 
     FROM track_clicks 
     WHERE `clicks_campaign_id`='$id' AND `clicks_click_type` = '2' AND `clicks_network_subid_data` = keywords.clicks_network_subid_data 
    ) AS offer_clicks 
   GROUP BY keywords.clicks_network_subid_data 
   ORDER BY keywords.COUNT(*) DESC
  </code></pre>
  
  <p>I also need to do a JOIN on another table to grab one more piece of data, but I think I can handle that once I get this part figured out.</p>