title: MySQL Multiple Group-By Count
body: <p>I'm trying to count some results from a query. I've been through the various examples on here but none of them are <em>adaptable</em> enough to do what I'm trying to do.</p>
  
  <p>If someone could take a look, point out where I'm going wrong, and kindly put me out of my misery, that would be wonderful. Thank-you.</p>
  
  <p><strong>Four Tables: Areas, Departments, Systems and Checks</strong></p>
  
  <p><em>Each Area has many Departments. Each Department has many Systems; and each System has many Checks. I'm trying to find out how many <strong>different</strong> Systems (by System_ID) have been checked in the previous week.</em></p>
  
  <p><strong>Current Query</strong></p>
  
  <pre><code>Select
  a.Area_Name,
  d.Department_Name,
  s.System_ID,
  s.System_Name,
  c.Check_Date
  
  from departments d
  
  left join areas a
  on a.Area_ID = d.Department_Area
  
  left join systems s
  on s.System_Department = d.Department_ID
  
  left join checks c
  on c.Check_System_ID = s.System_ID
  
  where Week(c.Check_Date) = Week(Now())
  and Year(c.Check_Date) = Year(Now())
  </code></pre>
  
  <p><em>Returns the Following :</em></p>
  
  <pre><code>Area_Name | Department_Name  |  System_ID   |  System_Name  |  Check_Date
  Area 1      Department 1        1              System 1        2017-02-27
  Area 1      Department 1        1              System 1        2017-02-27
  Area 1      Department 1        2              System 2        2017-02-27
  Area 1      Department 2        3              System 3        2017-02-27
  </code></pre>
  
  <p>What I'm trying to get to will group the areas, departments, systems etc. To show a count of how many <em>different</em> systems have been checked in the week.</p>
  
  <p><em>(I'm aware I haven't included any count or group-by in the query... I've left them out purposely to provide context. I have tried every possible way I can think but my knowledge is obviously lacking somewhere)</em></p>
  
  <p><strong>Desired Result</strong></p>
  
  <pre><code>Area_Name | Department_Name  |  Count_Checks
  Area 1      Department 1        2
  Area 1      Department 2        1
  </code></pre>
  
  <p>As you can see, the desired result has grouped the two <code>System 1</code> and treated them as 1 (as it's a single unique system), to show that over the last week, 2 different systems have been checked from department 1 and only 1 from department 2.</p>
  
  <p>If anyone can shed some light... please do!</p>
  
  <p>Thanks in advance for any help!</p>