Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Newbie needs help with SQL statement

Author  Topic 

Starting Member

2 Posts

Posted - 2014-10-07 : 13:05:34
I'm having a hard time figuring out the SQL statement needed for this project. I've been given the task to repair a query that has been implemented inside a piece of software that is around 15 years old.
I thnik that what I need is relatively simple for an SQL programmer.. but obviously not for me...

This is the current query :
SELECT Categories.Name AS Categories_Name,
Inventory.Block AS Inventory_Block,
Inventory.Section AS Inventory_Section,
Inventory.Polygone AS Inventory_polygone,
Inventory.Lot AS Inventory_lot,
ISNULL(Inventory.Hectares, 0) AS Inventory_Hectares,
ISNULL(Inventory.Meters, 0) AS Inventory_Meters,
ISNULL(Inventory.Points, 0) AS Inventory_points,
ISNULL(Prod_Emp.Cost, 0) AS Prod_Emp_Cost,
ISNULL(Prod_Emp.Revenue, 0) AS Prod_Emp_Revenue,
ISNULL(Prod_Emp.PercUnit, 0) AS Prod_Emp_PercUnit,
ISNULL(Inventory.Cost, 0) AS Inventory_Cost,
ISNULL(Inventory.Revenue, 0) AS Inventory_Revenue

FROM Categories LEFT OUTER JOIN Inventory
LEFT OUTER JOIN Production ON Inventory.ID = Production.Inventory_ID
LEFT OUTER JOIN Prod_Emp ON Production.Production_ID = Prod_Emp.Production_ID ON Categories.ID = Inventory.Category and inventory.year = '14'
The result gives me what I need but now, I also need to "group" some fields...
The only field that needs to "ADD" up is the "perc_unit" field. The rest of the fields have the same data.
ie :
Name Block Production_ID perc_unit
Treatment 7013 4815 0.75
Treatment 7013 4815 0.25
Treatment 8412 4816 1.00
Treatment 1800 4817 0.50
Treatment 1800 4817 0.50

Ideally, I'd like to end up with :
Name Block Production_ID perc_unit
Treatment 7013 4815 1.00
Treatment 8412 4816 1.00
Treatment 1800 4817 1.00

Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-07 : 13:43:36
ok, so add SUM(perc_unit) as sum_perc_unit to the select

and add a group by clause with every column in the select except for the perc_unit
Go to Top of Page

Starting Member

2 Posts

Posted - 2014-10-07 : 14:27:12
damn... I'm I THAT stupid ??
Go to Top of Page

- Advertisement -