title: Psql - ERROR: column "avclass" does not exist - selecting counts and percentages for columns that are grouped
body: <p>This is the query i'm trying to do. I'm no expert at psql.</p>
  
  <pre><code>SELECT avclassfamily, count(*) as countis, totalcount, percentage  
  FROM malwarehashesandstrings 
  where lol=True 
  group by avclassfamily 
  ORDER BY countis DESC;
  </code></pre>
  
  <p>avclassfamily = that string</p>
  
  <p>count(*) = total count where lol=true</p>
  
  <p>totalcount = count where lol=true or lol=false for that particular avclassfamily</p>
  
  <p>percentage = count as a percentage of totalcount</p>
  
  <p>This was the first command i used</p>
  
  <pre><code>SELECT avclassfamily, count(*) as countis 
  FROM malwarehashesandstrings 
  where lol=True 
  group by avclassfamily 
  ORDER BY countis DESC;
  </code></pre>
  
  <p>This is the output of the above command:</p>
  
  <pre><code> avclassfamily      | countis 
  ------------------------+---------
   autoit                 |     677
   cosmu                  |     226
   SINGLETON              |     223
   plingky                |     195
   fakepav                |     186
  </code></pre>
  
  <p>Here is my attempt to get closer to what I am looking for:</p>
  
  <pre><code>SELECT avclassfamily as avclass, count(*) as countis, 
         (select count(*) 
          FROM malwarehashesandstrings 
          where avclassfamily=avclass) as percent 
  from malwarehashesandstrings 
  where lol=True 
  group by avclassfamily 
  ORDER BY countis DESC;
  </code></pre>
  
  <p>The correct sql is:</p>
  
  <pre><code>select avclassfamily as avclass, 
         count(*) as countis,
         sum(case when avclassfamily = avclassfamily and lol=True then 1 else 0 end) as lolnumber , 
         case 
           sum(case when avclassfamily = avclassfamily and lol=True then 1 else 0 end) when 0 
             then NULL 
           else (sum(case when avclassfamily = avclassfamily and lol=True then 1 else 0 end) * 100 / count(*) ) 
         end as percentage
  from malwarehashesandstrings 
  group by avclassfamily 
  order by percentage desc NULLS LAST;
  </code></pre>