group by qst

In the following query I want to return as a last column the aggregate max
value of the OrderInList field.
input >>
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by *
order by OrderInList
output >>
Column 'tbl_activities.Activity_ID' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.
Can I achieve what I want by including the extra MaxOrderInList column
instead of returning an output param?
TIAalto wrote:
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
it
> is not contained in either an aggregate function or the GROUP BY clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>
Don't be lazy, specify your field list and stop using SELECT *. Any
non-aggregate field that is listed in your field list must also be
included in your GROUP BY clause, like this:
SELECT col1, col2, col3, MAC(col4)
FROM table
GROUP BY col1, col2, col3
It's generally considered bad practice to use "SELECT *".|||You can't GROUP BY *, and you shouldn't use SELECT * in production code
anyway.
It's really not that hard to generate a list of the columns in the table,
however what do you expect to happen here? If you have data like this:
ActivityID OrderInList
1 1
1 2
What is your desired output?
If you group by ALL columns, then your max(OrderInList) is meaningless.
Try it:
CREATE TABLE dbo.floob
(
ActivityID INT,
OrderInList INT
);
SET NOCOUNT ON;
INSERT dbo.floob(ActivityID,OrderInList)
SELECT 1,1
UNION ALL
SELECT 1,2;
SELECT
ActivityID, OrderInList,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID, OrderInList;
-- maybe what you meant was:
SELECT
ActivityID,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID;
GO
DROP TABLE dbo.floob;
GO
Without proper DDL, sample data and desired results, I have little else to
offer, except that grouping by all columns in the table doesn't make any
sense.
http://www.aspfaq.com/5006
"alto" <altodorov@.hotmail.com> wrote in message
news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>|||Last column is to return the same value for all records, like this
ActivityID, other cols..., MaxOrderInList
1 1,... 2
1 2,... 2
And BTW, in response to Tracy I'd already tried w/ all the fields in the
goup by - it wouldn't allow me to include an aggregate column in the list.
Again I could return the max OrderInList in an output param; I'm just asking
if the alternative above is possible.
Txs!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uX8gRLfmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> You can't GROUP BY *, and you shouldn't use SELECT * in production code
> anyway.
> It's really not that hard to generate a list of the columns in the table,
> however what do you expect to happen here? If you have data like this:
> ActivityID OrderInList
> 1 1
> 1 2
> What is your desired output?
> If you group by ALL columns, then your max(OrderInList) is meaningless.
> Try it:
> CREATE TABLE dbo.floob
> (
> ActivityID INT,
> OrderInList INT
> );
> SET NOCOUNT ON;
> INSERT dbo.floob(ActivityID,OrderInList)
> SELECT 1,1
> UNION ALL
> SELECT 1,2;
> SELECT
> ActivityID, OrderInList,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID, OrderInList;
> -- maybe what you meant was:
> SELECT
> ActivityID,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID;
> GO
> DROP TABLE dbo.floob;
> GO
>
> Without proper DDL, sample data and desired results, I have little else to
> offer, except that grouping by all columns in the table doesn't make any
> sense.
> http://www.aspfaq.com/5006
>
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
>|||"alto" <altodorov@.hotmail.com> wrote in message
news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> Last column is to return the same value for all records, like this
> ActivityID, other cols..., MaxOrderInList
> 1 1,... 2
> 1 2,... 2
> And BTW, in response to Tracy I'd already tried w/ all the fields in the
> goup by - it wouldn't allow me to include an aggregate column in the list.
What doesn't "wouldn't allow me" mean? Did you get an error message? What
was it? What code did you try?
I think you would have to do this with a correlated subquery, not group by.
For example, using the DDL and sample data I provided earlier:
SELECT
ActivityID,
OrderInList,
MaxOrderInList = (
SELECT MAX(OrderInList)
FROM dbo.floob
WHERE ActivityID = f.ActivityID
)
FROM
dbo.floob f;
A|||>> Last column is to return the same value for all records [sic], like this ..<<
Try a scalar subquery expression
SELECT a, b, c, ...
(SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
FROM Foobar;|||I mean I tried this query
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
max(OrderInList)
order by OrderInList
w/ this result:
Server: Msg 144, Level 15, State 1, Line 26
Cannot use an aggregate or a subquery in an expression used for the group by
list of a GROUP BY clause.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJsIHWfmGHA.856@.TK2MSFTNGP03.phx.gbl...
> "alto" <altodorov@.hotmail.com> wrote in message
> news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> What doesn't "wouldn't allow me" mean? Did you get an error message?
> What was it? What code did you try?
> I think you would have to do this with a correlated subquery, not group
> by. For example, using the DDL and sample data I provided earlier:
> SELECT
> ActivityID,
> OrderInList,
> MaxOrderInList = (
> SELECT MAX(OrderInList)
> FROM dbo.floob
> WHERE ActivityID = f.ActivityID
> )
> FROM
> dbo.floob f;
>
> A
>|||Bingo, it worked:
input >>
select Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated,
(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007') as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
--(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007')
OrderInList
order by OrderInList
output (see last column returned) >>
1309 81 1 NULL activite 1 emplacement 1 NULL NULL NULL NULL 2006-2007 1
1 2006-06-19 00:00:00.000 2006-06-24 00:00:00.000 2006-06-27 09:24:57.007 2
1310 81 1 NULL activite 2 emplacement 2 NULL NULL NULL NULL 2006-2007 1
4 2006-06-05 00:00:00.000 2006-06-17 00:00:00.000 2006-06-27 09:25:21.550 2
Txs!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151418422.623163.134150@.x69g2000cwx.googlegroups.com...
> Try a scalar subquery expression
> SELECT a, b, c, ...
> (SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
> FROM Foobar;
>|||>I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
And like I said before, selecting all columns and then aggregating one of
them doesn't make sense.
A|||alto wrote:
> I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by Activity_ID,
> Center_ID,
> Category_ID,
> Type_of_Activity ,
> [Description] ,
> Location_Activity ,
> Date_Activity ,
> Total_number_of_attendies ,
> Purpose_activity ,
> Fully_organized_by_org ,
> Partial_assistance_of_org ,
> BeginingDate_FY ,
> Fiscal_Year ,
> memo ,
> IsRequest ,
> ProposedGuest ,
> ActivityType ,
> DateStart ,
> DateEnd ,
> DateCreated ,
> max(OrderInList)
> order by OrderInList
> w/ this result:
> Server: Msg 144, Level 15, State 1, Line 26
> Cannot use an aggregate or a subquery in an expression used for the group
by
> list of a GROUP BY clause.
>
**THINK** about what you're doing here. Your aggregate value (MAX) is
calculated by grouping together like values from the table. The value
of MAX is unknown until that grouping has been done, so HOW can you
include it in the grouping criteria?
As I said before, STOP USING SELECT *, be specific about the fields that
you want returned by the SELECT, and include those fields (excluding the
aggregate) in your GROUP BY.