Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Group by clause and select list with expressions
Message
 
 
To
22/05/2006 15:10:33
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01124049
Message ID:
01124054
Views:
13
You have to use the same expression as in column list or you can use derived table
SELECT * FROM (	
	select cast(year(dinvoice)as char) as csalesyear, 
			replicate('0', 2 - len(cast(month(dinvoice) as char) )) +
				cast(month(dinvoice) as char) as csalesmonth
		from arinvc
		left outer join aritrs on (arinvc.ccustno = aritrs.ccustno and arinvc.cinvno = aritrs.cinvno)
		where  arinvc.lvoid = 0 and arinvc.cinvno <> 'BAL F/W' and
				(ARINVC.CSLPNNO = 'CAROL' and ARINVC.DINVOICE >= '2006-01-01' and ARINVC.DINVOICE <= '2007-01-31'
				and ARITRS.CITEMNO IN (SELECT citemno from sr_items_in_group where citemgroup='PROSTATFAMILY'))
				) dt1
group by csalesyear, csalesmonth	  
order by csalesyear, csalesmonth
>The following query fails in QA:
>
>
>select cast(year(dinvoice)as char) as csalesyear, replicate('0', 2 - len(cast(month(dinvoice) as char) )) +
>	cast(month(dinvoice) as char) as csalesmonth
>	 from arinvc
>left outer join aritrs on (arinvc.ccustno = aritrs.ccustno and arinvc.cinvno = aritrs.cinvno)
>where  arinvc.lvoid = 0 and arinvc.cinvno <> 'BAL F/W' and
>(ARINVC.CSLPNNO = 'CAROL' and ARINVC.DINVOICE >= '2006-01-01' and ARINVC.DINVOICE <= '2007-01-31'
>and ARITRS.CITEMNO IN (SELECT citemno from sr_items_in_group where citemgroup='PROSTATFAMILY'))
>group by year(dinvoice), month(dinvoice)	  order by year(dinvoice), month(dinvoice)
>The error messages are:
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>Server: Msg 8120, Level 16, State 1, Line 1
>Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>
>I never tried to use a group by clause, or select list clause with expressions. It looks like T-sql doesn't support this? Could I put the exact same expression in the group by as in the select list?
>
>In the meantime I am going to retrieve the results into a temp table with the column names csalesyear and csalesmonth, and then do my group by directly on csalesyear and csalesmonth, which I assume should work.
>
>Any help would be appreciated.
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform