Grouping by Free Text Field

Hi All,

I am in a dilemma, I have this database I need to pull a count from but then this is to be based on a free text field. The data is recorded as follows:

Product Name
==================
Delegate 1 Email Address
Delegate 1 Name
Delegate 1 Job Title
Delegate 2 Email Address
Delegate 2 Name
Delegate 2 Job Title

So from the data above the count should be '2', I just need to be able to get the count when looping through the database to know how many delegates a customer has filled in.

Help...
LVL 12
pigmentartsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
that is easy:
select count(*) from yourtable where product_name like '%Email Address%'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this sounds like some bad design, but anyhow:
can you clarify that the "count" will be based on the field's 2 first "words" aka everyting before the second space?
0
 
pigmentartsAuthor Commented:
Sorry didn't make it clear, that is what is stored in 1 field i.e  'Delegate 1 Email Address'
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I understood that. so, please clarify if the rule is: "group by the first 2 words in the field value, aka everything before the second space in the string"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
anyhow, here some example:
declare @demo_table table  ( product_name varchar(100) )
insert into @demo_table values ( 'Delegate 1 Email Address')
insert into @demo_table values ( 'Delegate 1 Name')
insert into @demo_table values ( 'Delegate 1 Job Title')
insert into @demo_table values ( 'Delegate 2 Email Address')
insert into @demo_table values ( 'Delegate 2 Name')
insert into @demo_table values ( 'Delegate 2 Job Title')

select left( product_name, charindex(' ', product_name, charindex(' ', product_name) + 1 ))
     , count(*)
  from @demo_table
group by left( product_name, charindex(' ', product_name, charindex(' ', product_name) + 1 ))

Open in new window

0
 
pigmentartsAuthor Commented:
Yes angelIII I need to group by say the number of email addresses
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, unless you clarify EXACTLY the rules to "count", I cannot give more concrete code than above.
0
 
pigmentartsAuthor Commented:
I need to group by the number of times 'Email Address' appears in the list so I can get the count in this case which should be '2'. Sorry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.