How to calculate one row field against another row

ASP Free Forums Sponsor:

October 30th, 2009, 07:07 AM

arsalan_2k

Registered User

Join Date: Oct 2009

Posts: 3

Time spent in forums: 19 m 26 sec
Reputation Power: 0

SQL Server 2005 - How to calculate one row field against another row

My title must be confusing but I don't know how to put it.
I have one column with county and another with client name.
i'm trying to query how many clients are in one county.

Client County

df surrey
sd surrey
ds Hampshire
gh London
dj London
kl Hampshire

can anyone please help me out with this.

thanks in advance.

Regards
Arsalan

October 31st, 2009, 04:06 AM

new learner

Contributing User

Join Date: May 2008

Location: India

Posts: 296

Time spent in forums: 5 Days 7 h 25 m 25 sec
Reputation Power: 127

Quote:

Originally Posted by arsalan_2k

Hi,

Just see whether this works, till the gurus come in

Code:

select county,count(client) as nos  from your_table_name group by county

Thanks

November 2nd, 2009, 04:31 AM

arsalan_2k

Registered User

Join Date: Oct 2009

Posts: 3

Time spent in forums: 19 m 26 sec
Reputation Power: 0

hi
thanks for your reply.

i manage to get the the statement work but because both those column are in different table so i'm inner join to show it, but i think the INNER join is not working ..

SELECT Description, COUNT(Company) AS NO.OFCLIENTS FROM locations, clients GROUP BY Description

INNER JOIN locations ON clients.LocationId=locations.LocationId

the table Locations contains column ( Description, LocationID)
and Clients contains column ( Company, LocationID)

many thanks

regards,
Arsalan

Quote:

Originally Posted by new learner

Hi,

Just see whether this works, till the gurus come in

Code:

select county,count(client) as nos  from your_table_name group by county

Thanks

November 2nd, 2009, 04:45 AM

micky

Couch Potato Wizard

Join Date: Jan 2005

Location: India

Posts: 12,259

Folding Points: 1480 Folding Title: Novice Folder

Time spent in forums: 5 Months 4 Days 2 m 48 sec
Reputation Power: 2179

Try this

Code:

SELECT L.Description, COUNT(C.Company) AS NO.OFCLIENTS FROM clients as C GROUP BY L.Description 
INNER JOIN locations as L ON C.LocationId=L.LocationId

I am not too sure about red part though, may be it should be in the end.

__________________
Laziness is my religion and Sunday is my God

Get the Mantra!

Viewing: ASP Free Forums > Database > Microsoft SQL Server > SQL Server 2005 - How to calculate one row field against another row

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!