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.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2009-08-10 : 04:24:26
|
Hi folks,I have a slight problem with a query I'm working on for report.The query is rather complex now and has SET statements etc. I'm trying to create a row number with in the SELECT list as follows:(select COUNT(*) from dbo.tblClientSite cntr where cntr.ClientAreaID = CS.ClientAreaID -- CS is ClientSite from outer query and cntr.SiteID <= CS.SiteID) as RowNumber -- For each row in the group, count the rows where the ID is the same and has a lesser value However, the problem I have is that the Outer query has to be have a GROUP BY clause. This is becuase the query displays a dataset that shows repairs made to a fashion brand's stores. I have to group the stores together to get the SUM cost of each store. Plus, to use the query above, I have to use the SiteID to create the Row Number. This table is the "detail" table.I don't think I can create a View because the SET statements etc. Does anyone know if there's a way to add the above code to the query while still having the GROUP BY clause.Any help is of course appreciated.Thanks |
|
|
|
|
|
using a subquery in select list with group by ?