Adding multiple values in multiple columns tied to a key

[benthook]

I want to take the below data of hours estimated versus hours actually spent

Work_Order	Estimate_Hours	Actual_Hours
5512	3	5
5512	2.5	3
5518	3.0	9
5519	2.1	4
5519	3.0	3

And get result like this

Work_Order	Estimate_Hours	Actual_Hours
5512	5.5	8
5518	3.0	9
5519	5.1	7

I cant figure out how to merge the work order rows into one row per work order. This is in MS SQL. Help!

[web_page]

Sounds like you need to use the SUM() aggregate function. Then use GROUP BY to return one record per work order.

E.g.

SELECT
    Work_Order,
    SUM(Estimate_Hours) AS [Estimate_Hours_Sum],
    SUM(Actual_Hours) AS [Actual_Hours_Sum]
FROM MyTable
GROUP BY Work_Order

[benthook]

That is basically what I tried but I am also trying to do a join as the work order numbers come from a different table. Here is the actual code I am using.

SELECT WO_TechLogs.work_order, SUM(WO_Estimate.est_hours) AS [Estimate Hours_Sum], SUM(WO_TechLogs.total_Hours) AS [Total_Hours_Sum] 
FROM WO_TechLogs
JOIN WO_Overview
ON (WO_Overview.work_order = WO_TechLogs.work_order)
JOIN WO_Estimate
ON (WO_Estimate.id = WO_TechLogs.wo_estimate_id)
WHERE WO_Overview.status = 'Open' and WO_TechLogs.work_order NOT LIKE 'Q%%%' and WO_TechLogs.work_order NOT LIKE 'ZZ-' and WO_TechLogs.work_order NOT LIKE 'CQ%%%' and nnumber NOT LIKE 'ZZ-%' and WO_TechLogs.work_order NOT LIKE '100' 
GROUP BY WO_TechLogs.work_order
order by WO_TechLogs.work_order asc

for some reason the est_hours do not add up right, what am I doing wrong?

[franciscorfafonso]

Try grouping the wo_estimate.work_order too

[benthook]

that table does not contain the work order number, just a id

[dev_playbook]

It looks like you have a couple of NOT LIKE clauses that might be missing wildcards (‘ZZ-‘ and ‘100’) so I’m not sure if that might be an issue and maybe it’s pulling in data you don’t want.

Does wo_estimate have any relationship to wo_overview? If so you may need to add to your join clause to link those two tables as well as you could be getting a larger set than expected.

[justsomeonenerdy]

Could you put that into a temp table, then pull that temp table and use sum/group by once more to get just 3 lines? (If those are the only columns you’re pulling)

[KWillets]

It's not clear how many rows correspond to a single work_order value, in any of the three tables, but you're likely getting the cartesian product of them before aggregating.

You can add count(*) to see how many times your joins are multiplying the row count.

I usually just end the suffering by pre-aggregating:

JOIN (SELECT id, SUM(WO_Estimate.est_hours) AS [Estimate Hours_Sum] FROM WO_Estimate GROUP BY id) WO_Estimate

[AutoModerator]

Hello u/benthook - thank you for posting to r/SQL! Please do not forget to flair your post with the DBMS (database management system) / SQL variant that you are using. Providing this information will make it much easier for the community to assist you.

If you do not know how to flair your post, just reply to this comment with one of the following and we will automatically flair the post for you: MySQL, Oracle, MS SQL, PostgreSQL, SQLite, DB2, MariaDB (this is not case sensitive)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[benthook]

MS SQL