How to sum and group two tables

Minotaur01 · #1 (**permalink**) 04-20-07, 10:05

Hello everyone,

I trying to write an SQL statement that summerized work orders i have two tables one is the work_orders and the second is the work_orders_jobs table.
What i want to do is to summerize and group the work_orders and work_orders_jobs at the same time.

eg.
From the work_orders table i need this

COUNT(id)
SUM(subtotal)
WHERE date > ""
GROUP BY date

From the work_orders_jobs table i need this

SUM(job_hrs) AS job_hrs_cust WHERE paytype = "C"
SUM(job_hrs) AS job_hrs_warr WHERE paytype = "W"
SUM(job_hrs) AS job_hrs_intr WHERE paytype = "I"

the WOJ table is linked to the WO table by a many to one releationship and the result i trying to get is:

COUNT(id) SUM(subtotal) SUM(job_hrs_cust) SUM(job_hrs_warr) SUM(job_hrs_intr)

I have tried a number of selects an joins and i cannot figure it out.

p.s. summerizing the WO is not a problem but i just can join the jobs table. i'm using MySQL 5.0.

Thanks

r937 · #2 (**permalink**) 04-20-07, 10:53

Code:

select workorders   
     , sum_subtotal 
     , job_hrs_cust
     , job_hrs_warr
     , job_hrs_intr
  from (
       select COUNT(id)       as workorders
            , SUM(subtotal)   as sum_subtotal 
         from work_orders 
        WHERE date > ""
       ) as WO
cross 
  join ( 
       select SUM(CASE WHEN paytype = 'C'    
                       THEN job_hrs END
                 ) AS job_hrs_cust
            , SUM(CASE WHEN paytype = 'W'    
                       THEN job_hrs END
                 ) AS job_hrs_warr
            , SUM(CASE WHEN paytype = 'I'    
                       THEN job_hrs END
                 ) AS job_hrs_intr
         from work_orders_jobs
       ) as WOJ

FYI, that WHERE date > "" condition doesn't make sense -- you're not storing your dates as strings, are you?

aschk · #3 (**permalink**) 04-20-07, 11:17

Any chance of a table layout + sample data. I would certainly like to see this one and attempt a solution. I know rudy has supplied one already but i'd like to see it in action

I'm interested in testing out rudy's solution and seeing if I can find an alternative (that works...)

Minotaur01 · #4 (**permalink**) 04-20-07, 11:42

r937:

thanks for the code and no i'm not storing my dates as strings i was just using the quotes as a value place holder.

Minotaur01 · #5 (**permalink**) 04-20-07, 12:02

aschk:

here is a quick table layout and some sample data. if you do find an alternative solution please post it, i would like to see what you came up with.

work_orders table:

Code:

CREATE TABLE `work_orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) default '0',
  `wo_date` datetime default NULL,
  `wo_status` varchar(1) default NULL,
  `invoice_date` datetime default NULL,
  `invoice_subtotal_cust` double default '0',
  `invoice_subtotal_warr` double default '0',
  `invoice_subtotal_int` double default '0',
  `invoice_tax_cust` double default '0',
  `invoice_tax_warr` double default '0',
  `invoice_tax_int` double default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 12288 kB; InnoDB free: 12288 kB; InnoDB free: 1';

INSERT INTO `work_orders` VALUES ('90148', '99877', '2007-01-02 07:46:48', 'F', '2007-01-02 16:07:27', '883.49', '0', '0', '108.5', '0', '0');

work_orders_jobs table:

Code:

CREATE TABLE `work_orders_jobs` (
  `id` varchar(12) NOT NULL,
  `wo_id` int(11) default NULL,
  `op_id` int(11) default NULL,
  `op_code` varchar(16) default NULL,
  `op_title` varchar(100) default NULL,
  `op_complaint` mediumtext,
  `op_cause` mediumtext,
  `op_correction` mediumtext,
  `bill_category` varchar(1) default NULL,
  `bill_hrs` float(8,3) default '0.000',
  `bill_rate` float(8,3) unsigned zerofill default '0000.000',
  `tech1_id` int(11) default NULL,
  `tech1_hrs` float(8,3) default NULL,
  `tech1_rate` float(8,3) default NULL,
  `tech2_id` int(11) default NULL,
  `tech2_hrs` float(8,3) default NULL,
  `tech2_rate` float(8,3) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `work_orders_jobs` VALUES ('90148-1', '90148', '1', '09TO1P01-DPF', 'DUST & POLLEN FILTER', null, null, null, 'C', '0.200', '0089.000', '60', '0.200', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-10', '90148', '10', '18TOZZ18', 'DESCALE BRAKE DRUMS', null, null, null, 'C', '0.600', '0089.000', '60', '0.600', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-2', '90148', '2', '13TO1P06-FI', 'FUEL INJECTOR FLUSH', null, null, null, 'C', '0.800', '0089.000', '60', '0.800', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-3', '90148', '3', '18TO1P06-BFF', 'BRAKE FLUID FLUSH', null, null, null, 'C', '0.800', '0089.000', '60', '0.800', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-4', '90148', '4', '12TO1P02-CF', 'REPLACE COOLANT', null, null, null, 'C', '0.800', '0089.000', '60', '0.800', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-5', '90148', '5', '12TO1P03-IC', 'INVERTER COOLANT', null, null, null, 'C', '0.800', '0089.000', '60', '0.800', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-6', '90148', '6', '10TO1P160-T1', 'MAINTENANCE SERVICE', null, null, null, 'C', '1.100', '0089.000', '60', '1.100', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-7', '90148', '7', '09TO1P02-AF', 'REPLACE AIR FILTER', null, null, null, 'C', '0.000', '0089.000', '60', '0.000', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-8', '90148', '8', '18TOZZ04', '4 WHEEL BRAKE SERV.', null, null, null, 'C', '1.400', '0089.000', '60', '1.400', '25.000', null, null, null);
INSERT INTO `work_orders_jobs` VALUES ('90148-9', '90148', '9', '18TOZZFR-04', 'MACHINE FRT ROTORS', null, null, null, 'C', '1.200', '0089.000', '60', '1.200', '25.000', null, null, null);

Minotaur01 · #6 (**permalink**) 04-20-07, 12:10

r937:

Quick question in the cross join the is no "ON WO.id = WOJ.wo_id" so how dose it join only those records selected by the "WHERE condition"

r937 · #7 (**permalink**) 04-20-07, 12:34

minotaur, how was i supposed to know why you want totals from one table and totals from another table on the same line of output?

but that's what the cross join does -- it "matches" all possible combinations of 1 row from the 1st query against 1 row from the other table, thus producing 1 row of output

as requested

Internet Services

Web Development

Digital Marketing

Consumer Tech