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

    Unanswered: Grouping Multiple Columns into One

    Hello everyone,

    Here is my problem. I have a database that has a workorder_jobs table.

    This is how the table is setup.

    id desc tech1_id tech1_hours tech1_rate tech2_id tech2_hours tech2_rate


    I'm trying with no success to create a SQL "Group By" statement that will produce this output:

    tech_id tech_hours

    Each job can have more than one tech but i need to group the tech columns as if they where the same column.


    thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    this is a classic case of incorrect database design!
    We call this a "1 to many" relationship.

    You need to rethink your table structue - this article is well worth 3 (or more) reads!

    If you have any more questions feel free to post them back here
    ~George
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Georgev is correct - you should really look at improving your database design. In the time being you can use the following code which I believe should work but I don't have a MySQL server at hand to test it:
    Code:
    select *
    from    (
              select  id, desc, tech1_id as tech_id, tech1_hours as tech_hours
              from    MyTable
              union
              select  id, desc, tech2_id, tech2_hours 
              from    MyTable
              ) as MyNewView;
    You should get a select showing id, desc, tech_id, tech_hours so now you can use a normal group by to get total hours per job:
    Code:
    select id, desc, sum(tech_hours)
    from    (
              select  id, desc, tech1_id as tech_id, tech1_hours as tech_hours
              from    MyTable
              union
              select  id, desc, tech2_id, tech2_hours 
              from    MyTable
              ) as MyNewView
    group by id, desc;
    Mike

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good suggestion Mike

    However: Pounding A Nail: Old Shoe or Glass Bottle?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2005
    Posts
    10
    Thanks for the information. And I know that in a perfect world there should be another table with tech info and job ids but it's just not worth the extra coding for three extra fields.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What extra coding?
    Just create another table and shift the info to that table with some queries. I certainly wouldn't call that extra coding, just simple movement. Only takes 5 minutes to create a table and shift it.
    As for coding into an application, change your SQL statement to use a JOIN or other similar syntax.

    As george says :
    Yes, yes it is!
    Don't EVER forgo a good DB design when you have the opportunity to change it to be correct at the start.

Posting Permissions

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