SQL COUNT issue - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL COUNT issue

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2008-10-29 : 05:24:47

I am trying to create a procedure that returns a town beginning with a specified letter. The procedure would list all of the occurences with the specified letter and how many times occurs.

This I can do fine. But I need to return the top 4 entries in terms of how many occurences only, if I add top 4 to this it just grabs the top 4 towns on the list :( I'm confused and don't get it

sp_count_towns
(
@theletter varchar(1)=''
)
AS
SELECT COUNT(tb_properties.prop_address3), prop_address3
FROM
tb_properties

WHERE
left(TB_properties.prop_address3,1) = @theletter
GROUP BY prop_address3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 05:27:59

[code]sp_count_towns
(
@theletter varchar(1)=''
)
AS
SELECT TOP 4 prop_address3,Occurance
FROM
(
SELECT COUNT(tb_properties.prop_address3) AS Occurance, prop_address3
FROM
tb_properties

WHERE
TB_properties.prop_address3 like @theletter + '%'
GROUP BY prop_address3
)t
ORDER BY Occurance DESC
GO[/code]

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2008-10-29 : 05:35:17

wow, so quick at replying, I am forever in your debt, thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 05:36:33

quote:
Originally posted by godspeedba

wow, so quick at replying, I am forever in your debt, thank you

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-29 : 05:40:47

[code]CREATE PROCEDURE sp_count_towns
(
@theLetter varchar(1) = ''
)
AS

SET NOCOUNT ON

SELECT TOP 4 WITH TIES
COUNT(*) AS Items,
prop_address3
FROM tb_properties
WHERE prop_address3 LIKE @theLetter + '%'
GROUP BY prop_address3
ORDER BY COUNT(*) DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources