title: SUM(COUNT()) in Oracle
body: <p>I'm trying to work out how to make a column based on the count of one column multiplied by the number in another column of a joined table.</p>
  
  <p>Below is what I've tried so far:</p>
  
  <p>Attempt 1:</p>
  
  <pre><code>select a.course_id, sum(count(*)*c.FEE) as "Income", c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
  from attendancecheck a
  inner join course c
  on a.course_id = c.course_id
  group by a.course_id, c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
  order by "Income"
  </code></pre>
  
  <p>I get error:    ORA-00937: not a single-group group function</p>
  
  <p>Attempt 2: </p>
  
  <pre><code>select a.course_id, sum(People_on_course * c.Fee) as "Income", c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
  from (SELECT COUNT(a.participant_id) as People_on_course
  from attendancecheck a)
  inner join course c
  on a.course_id = c.course_id
  group by a.course_id, c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF
  order by "Income"
  </code></pre>
  
  <p>I get error:    ORA-00904: "A"."COURSE_ID": invalid identifier</p>
  
  <p>If someone can guide me in the correct direction on this, that would be great. </p>
  
  <p>Cheers,
  Daniel</p>