Count Query

[enigma65]

I have a table that holds ecommerce receipt data and each row has a cust_id field. A cust_id may appear more than once in the table (i.e. the cutomer has several receipts in the table).

I need to count how many customers have only one receipt (shopped online only once) in the table as opposed to customers that may have more than one receipt.

 

[JayKusch]

Afternoon Folk ...

have a query that is bombing out w/ an error message that read:

Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8550, which is greater than the allowable maximum of 8094.


This is the Query:

SELECT DISTINCT p.Alias, p.Name, p.Description, m.Keywords,
s.store_image
FROM Partner p
NNER JOIN MerchantKeywordsAgr m ON p.PartnerId =
m.PartnerIdMerch
INNER JOIN StoresNew.dbo.stores_master_lo s ON p.Alias =
REPLACE(s.store_link, 'usb/ProxyFrameset.jsp?Vendor=', '')


Here is the table structure:

CREATE TABLE [dbo].[MerchantKeywordsAGR] (
[PartnerIdMerch] [numeric](12, 0) NOT NULL ,
[Keywords] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nbr_Of_Keywords] [numeric](18, 0) NULL
) ON [PRIMARY]


we do have records with over 7900 bytes in the Keywords field, so i cannot reduce the size of the field.


Any pointers would be great!

Thanks

 

[JayKusch]

Sorry about my question being posted here. i had your answer and a question of my own and got a bit turned around. Anyways ... try this :


SELECT COUNT(*)
FROM TableName
WHERE EXISTS (SELECT *
FROM TableName as t
WHERE TableName.cust_id = t.cust_id
GROUP BY t.cust_id
HAVING COUNT(t.cust_id) < 2)

 

[Dilyias]

Just a quick wondering, but would the above query be any faster if you replaced the second * with cust.id ?

i.e :

SELECT COUNT(*)
FROM TableName
WHERE EXISTS (SELECT TableName.cust_id
FROM TableName as t
WHERE TableName.cust_id = t.cust_id
GROUP BY t.cust_id
HAVING COUNT(t.cust_id) < 2)



Would that utilize an index better? (say if one was on cust_id)

 

[tlbroadbent]

Replacing the * with ID won't improve performance because SQL is just checking the existance of a matching record not returning a record set. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[Dilyias]

Just a quick wondering, but would the above query be any faster if you replaced the second * with cust.id ?

i.e :

SELECT COUNT(*)
FROM TableName
WHERE EXISTS (SELECT TableName.cust_id
FROM TableName as t
WHERE TableName.cust_id = t.cust_id
GROUP BY t.cust_id
HAVING COUNT(t.cust_id) < 2)



Would that utilize an index better? (say if one was on cust_id)

 

[Dilyias]

Tlbroadbent,

Thanks for the answer! I just thought that perhaps it would help it check for the existance of the record quicker. Guess not.

Sorry about that double post of mine.