Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    10

    Unanswered: How to sum and group two tables

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...)
    Last edited by aschk; 04-20-07 at 12:26.

  4. #4
    Join Date
    Dec 2005
    Posts
    10
    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.

  5. #5
    Join Date
    Dec 2005
    Posts
    10
    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);

  6. #6
    Join Date
    Dec 2005
    Posts
    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"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •