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 2005 Forums  Transact-SQL (2005)  Group By Question

Author  Topic  elmojo Starting Member 1 Post Posted - 2010-06-09 : 13:54:14 Example, let's say I have a table with 10 records that look like this: Type Price Quantity Item T 5.99 15 Socks T 4.49 10 T-Shirts T 6.99 20 Hats T 15.59 90 Pants S 22.50 30 Pants S 55.99 25 Dresses S 15.99 10 Socks C 22.50 10 Socks C 30.15 50 Hats I want to group by the item and calculate a weighted average. No problem. SQL below: SELECT SUM(Price * Quantity) / SUM(Quantity) AS WeightedAverage, Item FROM MyTable GROUP BY Item But what I really want to do is set the weighted average to a specified value if any item in the group is type 'C'. Similar to below which will not work: SELECT CASE WHEN Type = 'C' THEN 22.50 ELSE SUM(Price * Quantity) / SUM(Quantity) END AS WeightedAverage, Item FROM MyTable GROUP BY Item This obviously will not work because Type is not in the group by or aggregate function. There should be a function to accomplish this but there doesn't appear to be. WHY NOT! I'm sure I can figure out other ways to do this but it would make so much sense if there was some kind of aggregate function that did this. I know in the example above I could use MAX but it is not a "clean" solution because it will only work in certain instances. I wish there was a function similar to what I have below (and yes I know "ANY" is already a SQL keyword, but it's the most logical word to use in this instance): SELECT CASE WHEN ANY(Type) = 'C' THEN 22.50 ELSE SUM(Price * Quantity) / SUM(Quantity) END AS WeightedAverage, Item FROM MyTable GROUP BY Item My returned data should look like this: WeightedAverage Item 22.50 Socks 4.49 T-Shirts 22.50 Hats 17.32 Pants 55.99 Dresses The data have been disguised to protect the innocent.