Newbie needs help with SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. 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

Opusss
Starting Member

2 Posts

Posted - 2014-10-07 : 13:05:34

Hi,
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,
Prod_Emp.Production_ID,
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

gbritton
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

Opusss
Starting Member

2 Posts

Posted - 2014-10-07 : 14:27:12

damn... I'm I THAT stupid ??
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources