count of items - 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

SQL Server 2005 Forums

Transact-SQL (2005)

count of items

Author

Topic

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-16 : 23:15:41

hi all...
i have a query to select top 100 records satisfying my query.i want to get the number of items starting with each alphabet like:
A(25)
b(30)
c(20)
d(5)
e(20)...Is there any way out?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 23:50:28

How does your query look ?

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-16 : 23:58:36

set @Company='%'+@Company+'%';
set @Brands='%'+@Brands+'%';
set @category=@category+'%';
set @Published_Information='%'+@Published_Information+'%';
set @Address='%'+@Address+'%';
set @Tel='%'+@Tel+'%';
set @Fax='%'+@Fax+'%';
set @ForeName='%'+@ForeName+'%';
set @SurName='%'+@SurName+'%';
set @JobTitle='%'+@JobTitle+'%';
set @County='%'+@County+'%';

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT distinct top 100

Company_data.Serial,
Company_Data.Company_Name,
Categories.Category_Description,
Company_Data.Tel,
Company_Data.Email,
Company_Data.Fax,
Company_Data.Fax_Publish,
Company_Data.Phone_Publish,
Company_Data.PublishEmail,
Company_Data.PublishWeb,
Company_Data.Address1,
Company_Data.Address2,
Company_Data.Address3,
Company_Data.Address4,
Company_Data.Address1 + ',' + Company_Data.Address2 + ',' + Company_Data.Address3 + ',' + Company_Data.Address4 as Address,
Company_Data.Web_site,
Company_Data.Logo,

company_information.Information as description, Users.User_Status
FROM
Company_Data
left outer JOIN
Company_Categories ON Company_Categories.Serial = Company_Data.Serial
left outer JOIN
Categories ON Categories.Category_Code = Company_Categories.Category_Code
left outer JOIN
Company_Brands ON Company_Data.Serial = Company_Brands.Serial left outer JOIN
County ON Company_Data.County_Code = County.County_Code left outer JOIN
Company_Contacts ON Company_Data.Serial = Company_Contacts.Serial left outer JOIN

Company_Information ON Company_Data.Serial = Company_Information.Serial left outer JOIN
Industrial_Estates ON Company_Data.Industrial_Ref = Industrial_Estates.Industrial_Ref left outer JOIN
UserCompanies on
UserCompanies.CompanySerial = Company_Data.Serial
left outer JOIN

Users on
Users.UserId = UserCompanies.UserId

where

(Company_Brands.Brand like @Brands

or

Company_Data.Company_Name like @Company

or

Categories.Category_Description like @Published_Information

or

Company_Information.Information like @Published_Information

or

( CONVERT ( int , Company_Data.Number_Of_Employees ) >@Number_of_Employees_min and CONVERT ( int , Company_Data.Number_Of_Employees ) < @Number_of_Employees_max )

or

Company_Data.Address1 like @Address or
Company_Data.Address2 like @Address or
Company_Data.Address3 like @Address or

Company_Data.Address4 like @Address or
Company_Data.Address5 like @Address or
Company_Data.Address6 like @Address

or
Company_Data.Import=@Business_Import or
Company_Data.Export=@Business_Export or
Company_Data.Distribution=@Business_Manufacture or
Company_Data.Manufacture=1 or
Company_Data.[Service]=@Business_Service

or

Company_Data.Tel=@Tel
or
Company_Data.Fax=@Fax
or
Company_Contacts.Forename like @ForeName

or

Company_Contacts.Surname like @SurName
or
Company_Contacts.Job_Title like @JobTitle

or

County.County_Name like @County)
and

Company_Data.ActiveCompany =1
=====================================================================================
this query will return max 100 records. From that i want to get the count if items starting vth each alphabet...

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-17 : 00:01:54

the above query is to get the search result to be displayed in grid...i want also the count which i want to display in a treeview...plz reply using any simple query...i can work it out for my query...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 01:39:01


select left(col1,1),count(*) from yourtable group by left(col1,1)

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-17 : 02:03:48

thnx bro...that was really nice

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-17 : 03:59:07

Another way also try this

select substring(string,1,1) as string,count(*) as 'sum' from urtable group by substring(string,1,1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 04:03:09

select left(col1,1), count(*) from (select top 100 * from ... ) group by left(col1, 1)

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

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-17 : 04:15:23

select left(col1,1), count(*) from (select top 100 * from urtable) as t group by left(col1, 1)
or
select top 100 left(col1,1), count(*) from urtable group by left(col1, 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 07:51:11

I finding it hard to think that any alphabet has 100 characters, as in your second example

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