Grouping a select statement with Agg functions

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

General SQL Server Forums

New to SQL Server Programming

Grouping a select statement with Agg functions

Author

Topic

Noncentz
Starting Member

5 Posts

Posted - 2011-07-26 : 14:52:54

Good day everyone,

I am trying to group a query I created a few days back. I managed to get all of my relevant information in the select statement but now I have to use some aggregate functions in order to SUM a column and get the MAX date from another.

SUM(pu.number_of_orders) AS number_of_orders,
MAX(dp.computed_year_period) AS Period_Last_Sold

I figured this would be pretty easy but I have been proven wrong due to my lack of experience.

Here is my SELECT STATEMENT before I tried grouping it.

SELECT     
pu.item_id,
IM.item_desc, 
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage, 
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A,
IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand, 
SUM(pu.number_of_orders) AS number_of_orders,
MAX(dp.computed_year_period) AS Period_Last_Sold

FROM         
dbo.p21_view_inv_period_usage AS pu INNER JOIN dbo.p21_view_inv_loc AS IL
ON IL.location_id = pu.location_id AND IL.item_id = pu.item_id AND IL.inv_mast_uid = pu.inv_mast_uid 
INNER JOIN dbo.p21_view_demand_period dp 
ON pu.demand_period_uid = dp.demand_period_uid
INNER JOIN dbo.p21_view_inv_mast IM ON IM.inv_mast_uid = IL.inv_mast_uid
LEFT OUTER JOIN dbo.McCoy_Sales_Assembly_NOT AS SA ON SA.item_id = IM.item_id


-- Location and Year/Date Delimiter
WHERE
(pu.location_id = '3000') AND 
(pu.inv_period_usage + pu.scheduled_usage > 0) AND SA.A IS NULL

ORDER BY pu.item_id ASC

I do get the correct amount of records so I continued on and tried to group it here:

SELECT     
pu.item_id,
IM.item_desc, 
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage, 
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A,
IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand, 
SUM(pu.number_of_orders) AS number_of_orders,
MAX(dp.computed_year_period) AS Period_Last_Sold

FROM         
dbo.p21_view_inv_period_usage AS pu INNER JOIN dbo.p21_view_inv_loc AS IL
ON IL.location_id = pu.location_id AND IL.item_id = pu.item_id AND IL.inv_mast_uid = pu.inv_mast_uid 
INNER JOIN dbo.p21_view_demand_period dp 
ON pu.demand_period_uid = dp.demand_period_uid
INNER JOIN dbo.p21_view_inv_mast IM ON IM.inv_mast_uid = IL.inv_mast_uid
LEFT OUTER JOIN dbo.McCoy_Sales_Assembly_NOT AS SA ON SA.item_id = IM.item_id

GROUP BY

pu.item_id,
IM.item_desc, 
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage, 
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A
IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand 

-- Location and Year/Date Delimiter
HAVING
(pu.location_id = '3000') AND 
(pu.inv_period_usage + pu.scheduled_usage > 0) AND SA.A IS NULL

ORDER BY pu.item_id ASC

Now when I try to execute this I receive errors on these lines:

IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand,

I assume it is because the are aliased which means I cannot used the calculated columns in my select, which is a problem for me. If I try and comment out the calculated fields I receive errors because they are still in the select and I simply have no idea where to go from here. I suppose I could always create a new query and group from that but I would like it all to be in the same query if I could. I have been reading and apparently I can use a subquery to fix this but all I have at my disposal is this really crappy MCTS SQL training kit book and it does me no good.

Sorry this was so long but I figure more is better than less.

Thanks for everything you all do,

Antony

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 15:15:48

You can use calculated columns, but use the aliasing only in the select part.Remove the aliasing in the group by clause:


GROUP BY

pu.item_id,
IM.item_desc, 
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage, 
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A,
IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand

Noncentz
Starting Member

5 Posts

Posted - 2011-07-26 : 15:52:59

Thanks for the help, much appreciated!

When I remove my alias it did wonders, I am surprised I did not try this sooner. When I try to execute I get:

"Each GROUP BY expression must contain at least one column that is not an outer reference."

I googled the error of course and from what I read this error happens when you have a constant in the GROUP BY clause. From what I can tell I don't have anything like that in my query.

I now have:

SELECT
pu.item_id,
IM.item_desc,
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage,
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A,
IL.qty_on_hand * IL.moving_average_cost AS Total_Cost_On_Hand,
CONVERT(varchar, GETDATE(), 110) AS Run_Date,
pu.inv_period_usage + pu.scheduled_usage AS SumActualDemand,
SUM(pu.number_of_orders) AS number_of_orders,
MAX(dp.computed_year_period) AS Period_Last_Sold

FROM
dbo.p21_view_inv_period_usage AS pu INNER JOIN dbo.p21_view_inv_loc AS IL
ON IL.location_id = pu.location_id AND IL.item_id = pu.item_id AND IL.inv_mast_uid = pu.inv_mast_uid
INNER JOIN dbo.p21_view_demand_period dp
ON pu.demand_period_uid = dp.demand_period_uid
INNER JOIN dbo.p21_view_inv_mast IM ON IM.inv_mast_uid = IL.inv_mast_uid
LEFT OUTER JOIN dbo.McCoy_Sales_Assembly_NOT AS SA ON SA.item_id = IM.item_id

GROUP BY

pu.item_id,
IM.item_desc,
pu.location_id,
IL.inv_min,
IL.inv_max,
IL.primary_bin,
IL.date_created,
pu.scheduled_usage,
IL.moving_average_cost,
IL.product_group_id,
IL.standard_cost,
IM.product_type,
SA.A,
IL.qty_on_hand * IL.moving_average_cost,
CONVERT(varchar, GETDATE(), 110),
pu.inv_period_usage + pu.scheduled_usage

-- Location and Year/Date Delimiter
HAVING
(pu.location_id = '3000') AND
(pu.inv_period_usage + pu.scheduled_usage > 0) AND SA.A IS NULL

ORDER BY pu.item_id ASC

Noncentz
Starting Member

5 Posts

Posted - 2011-07-26 : 16:03:26

Found it! It was my CONVERT(varchar, GETDATE(), 110) column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources