select with TOP and COUNT

CandyMe

Hi, please help I wanna select top 5 from table satisfying a condition and at the same time count the number of rows returned based on selection. Select top 5 LastName, (Select count (*) from TBL where lastname = 'Cruz') as Count from TBL where lastname = 'Cruz' assuming there are only 3 rows with Cruz as latname result should be: Lastname | Count Cruz 3 Cruz 3 Cruz 3 I mean my query doesn't work and have researched over the internet. Please help. thank you. :((

Gerri

albCode

Select top 5 LastName, count (*) as [Count] from TBL where lastname = 'Cruz' group by lastname order by [Count] Desc

CandyMe

it doesn't work, seriously. I already copy pasted and been doing some workarounds on this. using sql 2005. or what's wrong really? :(( thank u

Gerri

albCode

Select top 5 LastName, count (*) as [Count] from TBL where lastname = 'Cruz' group by [Count]

Eric Dahlvang

CandyMe wrote:

Select top 5 LastName, (Select count (*) from TBL where lastname = 'Cruz') as Count from TBL where lastname = 'Cruz'

If this isn't working, there is some other problem.

CandyMe wrote:

I mean my query doesn't work and have researched over the internet.

Can you be a bit more specific as to what error message you are getting. Maybe give us some samples of the data. According to what you have showed us here, there is absolutely NO reason your query should'nt return what you expect.

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

CandyMe

I mean my query doesn't produce the result I'm expecting. For instance, I selected Top 5 with a given condition. But 3000 passed this condition. Rowcount should only be 5. Upon querying such Select top 5 LastName, (Select count (*) from TBL where lastname = 'Cruz') as Count from TBL where lastname = 'Cruz' it gives me: the top 5. but doesn't give me the correct count: 5 LastName | Count Cruz | 3000 Cruz | 3000 Cruz | 3000 Cruz | 3000 Cruz | 3000 Thank u.

Gerri

CandyMe

I figured it some 2 hours after my last reply. I decided to create a temp table where I'd do my select top then just count the number of rows in that temp table with the whereclause, ofcourse CREATE TABLE #RowCount(SubscriberId INT) EXEC ('INSERT #RowCount' + ' SELECT TOP ' + @Top + ' (SubscriberId) FROM ' + @TableName) --get total pages EXEC ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM #RowCount') --get total number of rows EXEC ('SELECT COUNT(*) FROM #RowCount') so if i selected top 10000 and only 3000 passed the condition totalrowcount is 3000; otherwise, if i selected top 2500 and 3000 passed the condition, totalrowcount is 2500 thanks! :suss: :omg:

Gerri

templarx

Or you can try something like this: SELECT sub.surname,count(*) as count FROM (select top 5 surname from myTable) sub GROUP BY sub.surname --[WHERE surname = 'CRUZ'] OUTPUT will bev [without the where clause] CRUZ 3 TOM 1 JACK 1 if you want to see all 5 rows [basically repeating ] SELECT sub3.surname,sub2.count FROM (select count(*) as count from (select top 5 surname from myTable where surname = 'CRUZ') sub) sub2 CROSS JOIN (select top 5 column_name from myTable where surname = 'CRUZ' ) sub3

CandyMe

thanks so much. well appreciated. :)

Gerri