Group by Restrictions?

Hi, Just a little doubt...
Is any difference or restriction there between using in the group by clause the columns as they are in the select list?

For example when I use an UPPER, CONVERT, etc.

Thanks : )

use pubs

--Case 1
select upper(title), type
from titles
group by upper(title), type

--Case 2
select upper(title), type
from titles
group by (title), type

Actually you don't have a GROUP BY query because a GROUP BY without an aggregate function is just a standard DISTINCT but SQL Server 2005 will not allow you to compile the code and ask you to add the aggregate function. Hope this helps.|||

In your case both the quires will be identical. (Upper & Lower)

When you use convert it may produce a different result..BUT YOU WONT GET ANY ERROR.

See the below sample,

Code Snippet

Create table #Test(

Date datetime,

StkQty int);

Insert into #Test Values('2007-01-01 10:00', 10)

Insert into #Test Values('2007-01-01 11:00', 100)

Insert into #Test Values('2007-01-02 12:00', 17)

Insert into #Test Values('2007-01-02 13:00', 13)

Select

Convert(varchar(10),Date,103)

,Sum(StkQty) as [Sum]

From

#test

Group By

Convert(varchar(10),Date,103)

/*

DateSum

01/01/2007 110

02/01/2007 30

*/

Select

Convert(varchar(10),Date,103)

,Sum(StkQty) as [Sum]

From

#test

Group By

Date

/*

DateSum

01/01/2007 10

01/01/2007 100

02/01/2007 17

02/01/2007 13

*/

|||

Yes I am sorry, it was a bad example. Suppose that I have made a select over some duplicated rows.

I need to make a kind of Catalog and want only distinct values, so as a way to do it I use the Group by Clause. Some times I use to apply "convert char", "case", "upper" over columns. I have seen some cases where Group By clause is showed exactly like the select list but just don't know the meaning.