Receiving <Unsupported Data Type> from Subquery

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

Receiving <Unsupported Data Type> from Subquery

Author

Topic

DotNetInt
Starting Member

9 Posts

Posted - 2007-04-07 : 23:22:29

I have a table that records sales leads and I need to calculate the total number of leads per user and the total amount owed, then then show only the users who have recieved 3 leads. The UserID is an integer.

Here is what i have:

SELECT * FROM
(
SELECT DISTINCT UserID,
(SELECT COUNT(*)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalLeads,
(SELECT SUM(Price)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalAmount
FROM LeadMatches AS L
)
WHERE TotalLeads = 3

This should work but it produced the following result:

UserID | TotalLeads | TotalAmount
---------------------------------------------------------------
<Unsupported Data Type> | 3 | 75.00
<Unsupported Data Type> | 2 | 50.00

I have no idea why the result produceing <Unsupported Data Type> for the UserID and have been unable to find a way around as of yet. Any help would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-08 : 00:40:56

Why not using JOINS rather than sub-query ?


SELECT * FROM
(
SELECT DISTINCT UserID,
(SELECT COUNT(*)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalLeads,
(SELECT SUM(Price)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalAmount
FROM LeadMatches AS L
) AS A
WHERE TotalLeads = 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-08 : 03:42:45

Why not this, which I think will perform better!

SELECT		UserID,
		COUNT(*) AS TotalLeads,
		SUM(Price) AS TotalAmount
FROM		LeadMatches
GROUP BY	UserID
HAVING		COUNT(*) = 3
ORDER BY	UserID

Peter Larsson
Helsingborg, Sweden

DotNetInt
Starting Member

9 Posts

Posted - 2007-04-08 : 18:17:26

Great, that works perfectly. Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources