Hi guys,

I'm trying to be clever with a query to add up credits from one table under categories from another. It's a pretty simple query and it almost works. Here's the details:

I've got an activities table and a categories table like so:

PHP Code:
CREATE  TABLE IF NOT EXISTS categories (
  
id INT NULL AUTO_INCREMENT ,
  
category VARCHAR(45NOT NULL ,
  
description VARCHAR(255NOT NULL ,
  
minimum DECIMAL(6,2NULL ,
  
maximum DECIMAL(6,2NULL ,
  
PRIMARY KEY (id) )
ENGINE InnoDB;

CREATE  TABLE IF NOT EXISTS activities (
  
id INT NULL AUTO_INCREMENT,
  
contact_id INT NOT NULL ,
  
category_id INT NOT NULL ,
  
credit_date DATETIME NOT NULL ,
  
credits DECIMAL(6,2NOT NULL ,
  
activity VARCHAR(45NOT NULL ,
  
notes VARCHAR(255NULL ,
  
PRIMARY KEY (idcontact_idcategory_id) )
ENGINE InnoDB

My ideal Query, returns the total credits per category ordered by categories... This is what I've done so far. It Adds up all of the credits for all categories and provides the first Category, minimum, maximum and description it comes across:

PHP Code:
SELECT categories.category AS category,  
       
SUM(activities.credits) AS credits
       
categories.minimum AS minimum
       
categories.maximum AS maximum,
       
categories.description AS description 
       FROM categories 
       INNER JOIN activities 
       ON categories
.id activities.category_id 
       WHERE contact_id 
$contact_id
       
AND EXTRACT(YEAR FROM activities.credit_date) = $report_year
       ORDER BY categories
.id;

#note: $contact_id and $report_year are variables like 1 and 2012. 
It returns this:
'Work Experience',24,'0.00','20.00','Work Experience description'
What I'm after should return something that looks roughly like this:

'Work Experience','8','0.00','20.00','Work Experience description'
'Education','4','0.00','20.00','Education description'
'Mentoring','12','0.00','40.00','Mentoring description'
Failing this, I can always use a query like the following and put it in a loop that runs for each category but I want to avoid that if I can (and I know I can):

PHP Code:

SELECT categories
.category AS category,  
        
SUM(activities.credits) AS credits
        
categories.minimum AS minimum
        
categories.maximum AS maximum,
        
categories.description AS description 
       FROM categories 
       INNER JOIN activities 
       ON categories
.id activities.category_id 
       WHERE contact_id 
$contact_id
       
AND EXTRACT(YEAR FROM activities.credit_date) = $report_year
       
AND categories.id $category_id;

#note: $contact_id, $report_year and $category_id are variables like 1, 2012 and 3. 
Anyway, I'm certain I've accomplished this in the past I just can't recall what I did.

Thanks for any help.

Andrew