Counting number of records using field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Counting number of records using field?

Author

Topic

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 12:10:03

I have two tables, the first is a list of product names (PRODUCT), the other is a list of text requests (REQUESTS). I'd like to count the number of times a product is requested in a request. Something like:

SELECT
PRODUCT.NAME,

(SELECT COUNT(*)
FROM REQUESTS AS TEMP_A
WHERE TEMP_A.QUESTION LIKE '* PRODUCT.NAME *')

FROM PRODUCT
GROUP BY PRODUCT

I'm not sure how to write the LIKE statement to find the string of PRODUCTS. For clarity, the PRODUCT list uses the same Product name multiple times, but with differing sizes, colors, etc (that's why the GROUP is there). Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 12:16:11

replace * with % and seeif that helps.

Jim

Everyday I learn something that somebody else already knew

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 12:19:10

The problem, I think, is that the quotes offset the PRODUCT.NAME as text instead of a field reference. Can I use wildcards in the statement w/o using quotes?

Should I be using a different function? CONTAINS seems likely, but I don't understand the syntax.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 12:51:47

The % is the wildcard for sql server. LIKE '% abc %' is different than LIKE '%abc%'. The former will look for 'abc' with a blank before it and after in the name field, the latter will look for 'abc' anywhere in the name field. I may not fully understand what you're trying do. Can you give an example of what's in TEMP_A.QUESTION and PRODUCT.NAME?

Jim

Everyday I learn something that somebody else already knew

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 13:04:47

Example:
Table 1 is the PRODUCTS table (~20,000 records), with fields:
PRODUCTNAME, COLOR, SIZE

Sample data:
BIKE, BLUE, 40
BIKE, BLUE, 45
BALL, Basketball, Regulation
BALL, BASEBALL, Regulation

Table 2 is the REQUESTS table (~6000 records) that stores all online requests, with fields:
EMAIL, DATE, REQUEST_TEXT

Sample data:

bob@yahoo.com, 4/15/2010, I want a bike.
bill@yahoo.com, 4/16/2010, I like basketball.
sara@yahoo.com, 5/1/2010, Do you have bikes?

So what I want to do is to summarize the PRODUCTS table:

BIKE
BALL

and then count the number of times the PRODUCT shows up in the REQUESTs

BIKE (2)
BALL (1)

But I don't know how to write the syntax so that I can summarize all of the product types, and then uses that shortened list

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 13:30:07

SELECT p.Productname,count(*) as [Count]
FROM
PRODUCTS p
INNER JOIN
REQUESTS r
ON
r.request_text like '%'+p.productname + '%'

Jim

Everyday I learn something that somebody else already knew

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 14:59:06

"Cannot Join on Memo, OLE, or Hyperlink Object."

Thanks for the help so far, but we're not there yet.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 15:29:17

I'm assuming then that request_text is an actual TEXT field, you could try
ON
convert(varchar(max),r.request_text) like '%'+p.productname + '%'

is there an actual need for request_text to be text field (more than 8000 characters)? Also, to avoid any future surprises, please provide DDLs for all the involved tables with some sample data for each.

Jim

Jim

Everyday I learn something that somebody else already knew

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 15:45:57

I'm not sure if the field 'needs' to be greater than 8000, I'm just getting imports from a external website. I'll try the conversion. (Sorry about the memo/text thing, didn't think for a second that it wouldn't be text)

HomerJ
Starting Member

21 Posts

Posted - 2010-05-27 : 16:05:43

Victory! By making a temporary table of REQUEST and converting the REQUEST_TEXT field to text (instead of memo) I was able to run your statement. Thanks Jim for the help!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 17:42:45

Glad I could help!

jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources