Grouping Multiple Columns into One

Minotaur01 · #1 (**permalink**) 08-14-07, 21:48

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.

gvee · #2 (**permalink**) 08-15-07, 05:45

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

mike_bike_kite · #3 (**permalink**) 08-15-07, 06:00

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

gvee · #4 (**permalink**) 08-15-07, 07:22

Good suggestion Mike

However: Pounding A Nail: Old Shoe or Glass Bottle?

Minotaur01 · #5 (**permalink**) 08-21-07, 00:03

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.

gvee · #6 (**permalink**) 08-21-07, 03:21

Yes, yes it is!
Another article worth a read

aschk · #7 (**permalink**) 08-21-07, 05:18

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 :

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech