I'm basically after the following:

Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don't.

The problem I'm having is that I can't figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job.

Database:

Code:
-- MySQL dump 10.11
--
-- Host: localhost    Database: dbtemp
-- ------------------------------------------------------
-- Server version	5.0.83-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tbl_analysis`
--

DROP TABLE IF EXISTS `tbl_analysis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_analysis` (
  `analysis_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `analysis_gross` int(10) default NULL,
  `analysis_net` int(10) default NULL,
  PRIMARY KEY  (`analysis_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_analysis`
--

LOCK TABLES `tbl_analysis` WRITE;
/*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */;
INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250);
/*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_customer`
--

DROP TABLE IF EXISTS `tbl_customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_customer` (
  `cust_id` int(20) unsigned NOT NULL auto_increment,
  `cust_name` varchar(255) default NULL,
  PRIMARY KEY  (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_customer`
--

LOCK TABLES `tbl_customer` WRITE;
/*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */;
INSERT INTO `tbl_customer` VALUES (1,'Customer A');
/*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice`
--

DROP TABLE IF EXISTS `tbl_invoice`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice` (
  `invoice_id` int(20) unsigned NOT NULL auto_increment,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_proj_id` int(20) unsigned default NULL,
  `invoice_date` date default NULL,
  `invoice_total` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default NULL,
  PRIMARY KEY  (`invoice_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_proj_id` (`FK_proj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice`
--

LOCK TABLES `tbl_invoice` WRITE;
/*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */;
INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent');
/*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice_item`
--

DROP TABLE IF EXISTS `tbl_invoice_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice_item` (
  `invoice_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_invoice_id` int(20) unsigned default NULL,
  `invoice_item_price` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default 'Pending',
  PRIMARY KEY  (`invoice_item_id`),
  KEY `FK_job_id` (`FK_job_id`),
  KEY `FK_invoice_id` (`FK_invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice_item`
--

LOCK TABLES `tbl_invoice_item` WRITE;
/*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */;
INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent');
/*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_job`
--

DROP TABLE IF EXISTS `tbl_job`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_job` (
  `job_id` int(20) unsigned NOT NULL auto_increment,
  `FK_proj_id` int(20) unsigned default NULL,
  `FK_langt_id` int(20) unsigned default NULL,
  `job_title` varchar(255) default NULL,
  `job_pages` int(10) default '1',
  `job_complete` enum('Yes','No') default 'No',
  `job_complete_date` timestamp NULL default NULL,
  PRIMARY KEY  (`job_id`),
  KEY `FK_proj_id` (`FK_proj_id`),
  KEY `FK_langt_id` (`FK_langt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_job`
--

LOCK TABLES `tbl_job` WRITE;
/*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */;
INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00');
/*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_language`
--

DROP TABLE IF EXISTS `tbl_language`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_language` (
  `lang_id` int(20) unsigned NOT NULL auto_increment,
  `langname` varchar(255) default NULL,
  PRIMARY KEY  (`lang_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_language`
--

LOCK TABLES `tbl_language` WRITE;
/*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */;
INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR');
/*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_project`
--

DROP TABLE IF EXISTS `tbl_project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_project` (
  `proj_id` int(20) unsigned NOT NULL auto_increment,
  `proj_title` varchar(255) default NULL,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_langsid` int(20) unsigned default NULL,
  `proj_start_date` timestamp NULL default NULL,
  `proj_end_date` timestamp NULL default NULL,
  `proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL,
  PRIMARY KEY  (`proj_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_langsid` (`FK_langsid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_project`
--

LOCK TABLES `tbl_project` WRITE;
/*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */;
INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete');
/*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task`
--

DROP TABLE IF EXISTS `tbl_task`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task` (
  `task_id` int(20) unsigned NOT NULL auto_increment,
  `task_title` varchar(50) default NULL,
  PRIMARY KEY  (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task`
--

LOCK TABLES `tbl_task` WRITE;
/*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */;
INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate');
/*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task_item`
--

DROP TABLE IF EXISTS `tbl_task_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task_item` (
  `task_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_task_id` int(20) unsigned default NULL,
  `status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending',
  `task_complete_date` timestamp NULL default NULL,
  `task_order` int(20) unsigned default NULL,
  PRIMARY KEY  (`task_item_id`),
  KEY `FK_task_id` (`FK_task_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task_item`
--

LOCK TABLES `tbl_task_item` WRITE;
/*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */;
INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4);
/*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-30 18:59:47
QUERY:
Code:
SELECT 			DATE_FORMAT(proj_end_date, '%m') as month_number,
  				MONTHNAME(proj_end_date) AS the_month,
				YEAR(proj_end_date) AS the_year,
				SUM(analysis_net) as analysis_net,
				SUM(analysis_gross) as analysis_gross,
				SUM(job_pages) as job_pages,
				SUM(invoice_item_price) as total, 
CASE			        WHEN task_title='Typesetting'
				AND ti1.status='Complete'
				THEN 'Typeset'
				ELSE 'Non-Typeset'
				END AS 'JobType',
				langname
FROM 			tbl_job as j
INNER JOIN 			tbl_task_item as ti1
				ON ti1.FK_job_id = j.job_id
INNER JOIN 			tbl_task as t
				ON t.task_id = ti1.FK_task_id
INNER JOIN 			tbl_invoice_item as ii
				ON ii.FK_job_id = j.job_id
INNER JOIN 			tbl_invoice as i
				ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 			tbl_analysis as a
				ON a.FK_job_id = j.job_id
INNER JOIN			tbl_language as l
				ON l.lang_id = j.FK_langt_id
INNER JOIN			tbl_project as p 				 				
				ON p.proj_id = j.FK_proj_id  
WHERE 			p.FK_cust_id = 1 				
				AND proj_status='Complete' 
GROUP BY			the_year, 				
				month_number, 				
				JobType, 				
				langname 
ORDER BY			the_year DESC, 				
				month_number DESC, 				
				JobType DESC, 				
				langname ASC