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:
CREATE TABLE IF NOT EXISTS categories (
id INT NULL AUTO_INCREMENT ,
category VARCHAR(45) NOT NULL ,
description VARCHAR(255) NOT NULL ,
minimum DECIMAL(6,2) NULL ,
maximum DECIMAL(6,2) NULL ,
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,2) NOT NULL ,
activity VARCHAR(45) NOT NULL ,
notes VARCHAR(255) NULL ,
PRIMARY KEY (id, contact_id, category_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:
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):
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
