Using a subquery in select list with a GROUP BY clause?

Question

Using a subquery in select list with a GROUP BY clause?

cidr

Ten Centuries

Points: 1353
More actions
August 10, 2009 at 2:38 am

#73309

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cidr Ten Centuries Points: 1353 More actions · Answer 2

Hi, thanks for the response. I managerd to sort my problem out by using a view instead. The code's a bit convoluted so I think it's best if I don't display.

Thanks again

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 3

Grasshopper,

People other than the original question poster learn from threads and the code posted, so in the future it would be helpful if you could post your code.

Considering how willing people are to help on SSC it's not too much to ask is it.

Thanks....Jason

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

pwatson (8/10/2009)
Hi, thanks for the response. I managerd to sort my problem out by using a view instead. The code's a bit convoluted so I think it's best if I don't display.

Heh... we're all grownups and we've all seen convoluted code before. No animals will be harmed in the making of the movie, either. Post the code, please. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jason (8/11/2009)
Grasshopper

No one named 'Grasshopper' here. That's his forum ranking, just as yours is 'Valued Member'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 6

Doh! I realised that almost the exact second I clicked on 'Post Reply'.

Jason

---