November 30, 2010 at 11:04 am
Hi
Im trying to find a way to count all the records in a number of fields
What i want is to count all the telephone numbers in a particular table
So if i had table1
ID Name Tel1 Tel2 Tel3
1 Fred 0129 0323
2 Bob 01982 01929
3 Sue 02198 09329 92191
i would get a result of 7
Can anyone help
thanks
Simon
November 30, 2010 at 11:15 am
I believe the following would suffice:
select count(tel1) + count(tel2) + count(tel3) from table1
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
November 30, 2010 at 11:16 am
First, rebuild the table so it's rows instead of columns on the multiple phone numbers. That's going to make things easier and better in the database all around. (Take a look at "normalization" in Wikipedia. It had a good article on it last time I checked [subject to change without notice, as always with wikis]. That'll explain why and how on this point.)
To query this table, you could count phone1, then add that to phone2, then to phone3.
select count(distinct Tel1) + count(distinct Tel2) + count(distinct Tel3)
from dbo.Table1;
If the blank numbers are NULL, that should give you what you need. Otherwise, you'll have to build three separate inline queries and add those up, which would allow you to use a separate Where clause for each column.
But really, normalize the table.
create table dbo.Phones (
ID int identity primary key,
PersonID int not null references dbo.People(PersonID) on delete cascade,
PhoneType varchar(20),
PhoneNumber varchar(20));
If you like, you can put a variety of constraints on that, like making PersonID and PhoneType be unique (but what do you do if someone has two different business numbers, for example). You can make the primary key something other than an identity. You could make the clustered index be the PersonID, which would speed most lookups in most databases. You could make the type be a lookup to a reference table that lists "home", "fax", "cell", "work", etc. And so on. This is just the skeleton of such a table. And it assumes a People table.
Querying such a table to find how many phone numbers you have is trivial. You can break it down by type. You can check for duplicates. You can do a variety of things that you simply cannot do, or can only do with great effort, in the multi-column table you have in your example.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 11:17 am
thank you 🙂
Simon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply