title: Mysql count frequency of every value with grouping by another values (two dimensional table)
body: <p>Maybe my English is not good enough to formulate the header (you're welcome to edit it and make more understandable) or it's because I'm googling different forms of it all day, but the task sounds very easy. Say I have a simple unsorted table about publications of 2013</p>
  
  <pre><code>+----------+--------------+-----------+
  | company  |   magazine   |   month   |
  +----------+--------------+-----------+
  | Google   | NatGeo       | march     |
  | Intel    | Playboy      | january   |
  | Google   | NatGeo       | february  |
  | IBM      | LinuxJournal | february  |
  | IBM      | NatGeo       | march     |
  | ...      | ...          | ...       |
  </code></pre>
  
  <p>, etc. So every listed company has articles in different magazines. I cannot figure out how to count numbers of all articles in each magazine grouping by company to get this:</p>
  
  <pre><code>+--------+--------+---------+--------------+-----
  |        | NatGeo | Playboy | LinuxJournal | ...
  +--------+--------+---------+--------------+-----
  | Google |   2    |   0     |      1       |
  +--------+--------+---------+--------------+-----
  | IBM    |   1    |   2     |      1       |
  +--------+--------+---------+--------------+-----
  | Intel  |   0    |   3     |      0       |
  +--------+--------+---------+--------------+-----
  | ...    |        |         |              |
  </code></pre>
  
  <p>It describes that Google had two articles in NatGeo, one in LinuxJournal and zero in Playboy during last year. Number of companies is unknown, as well as number of magazines. Not all companies have articles in same magazines or in all magazines. Of course, names of magazines and companies are unique.</p>
  
  <p>If you really know how to easily do it using only SQL*, you can later give me a piece of advice, where to put another possible clause <code>WHERE month='...'</code>, but it's not really important.</p>
  
  <hr>
  
  <p>*I can do it only using two queries and PHP loop</p>
  
  <p><strong>EDIT</strong> One angry guy assumed I didn't even try to do it by myself, so I have to present you a small part of my unsuccessful attempts. I started with basics:</p>
  
  <pre><code>SELECT company, COUNT(magazine) FROM `journals` WHERE magazine='somemagazine' GROUP BY company
  </code></pre>
  
  <p>It works predictably fine</p>
  
  <p>Then I've tried to add at least one more magazine. </p>
  
  <pre><code>SELECT j1.company, COUNT(j1.magazine) AS SomeMagazine, COUNT(j2.magazine) AS AnotherMagazine FROM `journals` j1, `journals` j2 WHERE j1.magazine='SomeMagazine' AND/OR j2.magazine='AnotherMagazine' GROUP BY j1.company
  </code></pre>
  
  <p>The idea was to query the same table twice. Surprisingly it also works, but multiplies all results (both columns have the same values in rows). I knew it's a wrong direction, so I've tried to left-inner-outer join my first table with the same query but another magazine. No luck. I've always got longer table (it just puts more rows to the end) or multiplied values. I've tried to <code>UNION</code> queries changing magazine's name every next string. Again no luck.</p>
  
  <p>Yep, I knew about all this <code>@</code> and <code>@@</code> stuff and @eggyal's solution is likely correct, but I still wonder if it's possible to perform such an easy action with easy statements... maybe for two certain magazines for a start.</p>