Cannot perform aggregate funtion error

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

SQL Server 2005 Forums

Transact-SQL (2005)

Cannot perform aggregate funtion error

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 22:35:14

Hi,

When I use the below select, I get the error

Msg 130, Level 15, State 1, Line 182
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Is it true that we cannot have an aggregate function like AVG performed on another aggregate like COUNT in a select?

Initally I had the column that aggregated (AVG) stored in a temp table but that caused cross joins and messed up my groupings.

Hence I am trying to have all columns in one table to avoid repeated dup values.

Thanks

SELECT DISTINCT
MISInquiryID = inq.MISInquiryID,
ProductID = iql.ProductID,
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = @SizeBucket,
DealerCnt = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
IsBuy = @IsBuy,
--Maturity = @Maturity,
Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,

--ABS(year(iss.Maturity) - year(inq.InquiryDate)) AS Diff
ListID = InquiryListID, --Adding below for Average Dealer Response Count
DealerBucket = CASE
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) >=1 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 5 THEN '>=1 & <=5 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 5 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 10 THEN '>=6 & <=10 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 10 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=15 THEN '>=11 & <=15 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 15 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 20 THEN '>=16 & <=20 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 20 THEN '>20 Dealers'
END,
AvgDlrCnt = CAST(AVG(convert(float, COUNT(DISTINCT res.CPPivotalCompanyID))) AS decimal (10,2)),
AvgRspCntWithLevel = CAST(AVG(convert(float, COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END))) AS decimal (10,2))

INTO #ResponseCnt
FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

LEFT JOIN
Trade trd
ON
iql.MISInquiryID = trd.MISInquiryID
and iql.LegSequence = trd.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
iql.ProductID = prd.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
rsl.MISResponseID = res.MISResponseID
and rsl.MISInquiryID = iql.MISInquiryID
and rsl.LegSequence = iql.LegSequence
INNER JOIN
#ClientTBL clnttbl

ON
clnttbl.ClientID = trd.PPivotalCompanyID
INNER JOIN
pivotal..Company dlr
ON
dlr.Company_Id = res.CPPivotalCompanyID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and inq.InquiryType = 'F'
and trd.IsBuy = CASE @IsBuy WHEN 'Bid' THEN 1 WHEN 'Offer' THEN 0 ELSE trd.IsBuy END
and iql.USDSize between @LowSize and @HighSize
and ABS(year(iss.Maturity) - year(inq.InquiryDate)) BETWEEN @LowMaturity and @HighMaturity

GROUP BY
inq.MISInquiryID,
inq.InquiryListID,
iql.ProductID,
prd.Description,
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 02:09:58

yup..its true. what you can do is wrap your first query containing COUNT() inside a derived table and then use AVG over it like


SELECT other columns, CAST(AVG(convert(float,DlrCnt)) AS decimal (10,2)), 
CAST(AVG(convert(float, RspCntWithLevel )) AS decimal (10,2))
FROM
(
SELECT	DISTINCT	
MISInquiryID	= inq.MISInquiryID,
ProductID	 = iql.ProductID,
Product = prd.Description,
SizeOrder = CASE 
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket	 = @SizeBucket,
DealerCnt	 = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt	 = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
IsBuy = @IsBuy,
--Maturity = @Maturity,
Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,

--ABS(year(iss.Maturity) - year(inq.InquiryDate)) AS Diff
ListID	 = InquiryListID, --Adding below for Average Dealer Response Count 
DealerBucket = CASE 
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) >=1 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 5 THEN '>=1 & <=5 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 5 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 10 THEN '>=6 & <=10 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 10 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=15 THEN '>=11 & <=15 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 15 AND COUNT(DISTINCT res.CPPivotalCompanyID) <= 20 THEN '>=16 & <=20 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 20 THEN '>20 Dealers' 
END,
DlrCnt	 = COUNT(DISTINCT res.CPPivotalCompanyID),
RspCntWithLevel = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END)


INTO	#ResponseCnt
FROM
Inquiry	 inq

INNER JOIN
InquiryLeg	 iql
ON
inq.MISInquiryID	 = iql.MISInquiryID

LEFT JOIN
Trade	 trd
ON
iql.MISInquiryID = trd.MISInquiryID	
and	iql.LegSequence = trd.LegSequence	
and	trd.IsVolume	 = 1

INNER JOIN
Product prd
ON
iql.ProductID = prd.ProductID 

INNER JOIN
#ProductTBL prdtbl 

ON 
prdtbl.ProductID = prd.ProductID 

INNER JOIN
Issue	 iss
ON
iss.MISIssueID	 = iql.InstrumentMISIssueID

INNER JOIN
Response	 res
ON
res.MISInquiryID = iql.MISInquiryID	
INNER JOIN
ResponseLeg	 rsl
ON
rsl.MISResponseID = res.MISResponseID	
and	rsl.MISInquiryID = iql.MISInquiryID 
and	rsl.LegSequence = iql.LegSequence	
INNER JOIN
#ClientTBL clnttbl 

ON 
clnttbl.ClientID = trd.PPivotalCompanyID 
INNER JOIN
pivotal..Company	 dlr
ON
dlr.Company_Id	 = res.CPPivotalCompanyID 

WHERE
iql.IsVolume	 = 1
and	inq.IsVolume	 = 1
and	inq.InquiryDate	 BETWEEN @StartDate and @EndOfEndDate
and	inq.InquiryType	 = 'F'
and trd.IsBuy	 = CASE @IsBuy WHEN 'Bid' THEN 1 WHEN 'Offer' THEN 0 ELSE trd.IsBuy END
and iql.USDSize	 between @LowSize and @HighSize
and ABS(year(iss.Maturity) - year(inq.InquiryDate)) BETWEEN @LowMaturity and @HighMaturity

GROUP BY
inq.MISInquiryID,
inq.InquiryListID,
iql.ProductID,
prd.Description,
CASE 
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END 
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 18:41:43

When you think about it, it makes little sense to use AVG(COUNT()). The COUNT() gives you the number of matching rows for each group. But it's a singular value. So it makes no sense to aggregate it any further. Aggregates work on a range of values, so it makes no sense to pass it what can only be a singular.

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-28 : 10:19:28

I will verify this need with Users although they still want to see the values for Avg for Dealer and Responses.

I tried the below and get this error. What am I doing wrong?

Msg 156, Level 15, State 1, Line 222
Incorrect syntax near the keyword 'INTO'.

DECLARE
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'
@ClientType varchar(50), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@Product varchar(50), -- '100' map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100), --<=1MM >1MM
@Maturity varchar(100)

SET @StartDate = '20100101'
SET @EndDate = '20100201'
SET @ClientType = '154246'
SET @Product = '154247'
SET @ListID = 'All Inquiries'
SET @IsBuy = 'Bid/Offer'
SET @SizeBucket = 'All Sizes'
SET @Maturity = '<= 1 Year'

/*IF OBJECT_ID('dbo.rptAvgDealerResponse') IS NOT NULL
BEGIN
DROP PROC dbo.rptAvgDealerResponse
IF OBJECT_ID('dbo.rptAvgDealerResponse') IS NOT NULL
PRINT '<<< FAILED DROPPING PROC dbo.rptAvgDealerResponse >>>'
ELSE
PRINT '<<< DROPPED PROC dbo.rptAvgDealerResponse >>>'
END
GO
CREATE PROCEDURE [dbo].[rptAvgDealerResponse]
(
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'
@ClientType varchar(50), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@Product varchar(50), -- '100' map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100), --<=1MM >1MM
--@DealerBucket varchar(100), -->=1 & <=5 Dealers >=6 & <=10 Dealers >=11 & <=15 Dealers >=16 & <=20 Dealers >20 Dealers
@Maturity varchar(100) --<= 1 Year

)

AS
SET NOCOUNT ON

if exists
(select * from sysobjects where id = object_id(N'[#rptSalesReportByClientPlan]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)*/

DECLARE
@EndOfEndDate datetime,
@ProductDesc varchar(500),
@ProductID int,
@ProductCount int,
@ClientDesc varchar(100),
@DealerBucket varchar(100)

EXECUTE
misReportDate
@StartDate = @StartDate OUTPUT,
@EndDate = @EndDate OUTPUT,
@EndOfEndDate = @EndOfEndDate OUTPUT

if object_id ('tempdb..#ResponseCnt') is not null
drop table #ResponseCnt
if object_id ('tempdb..#DealerTemp') is not null
drop table #DealerTemp
if object_id ('tempdb..#HitRatio') is not null
drop table #HitRatio
if object_id ('tempdb..#AvgDealerResponse') is not null
drop table #AvgDealerResponse
if object_id ('tempdb..#Maturity') is not null
drop table #Maturity
if object_id ('tempdb..#ProductTBL') is not null
drop table #ProductTBL
if object_id ('tempdb..#ClientTBL') is not null
drop table #ClientTBL
if object_id ('tempdb..#ProductName') is not null
drop table #ProductName
if object_id ('tempdb..#ClientTypeName') is not null
drop table #ClientTypeName
if object_id ('tempdb..#List') is not null
drop table #List

--use this for Product and Client and Size display on the reporting tool
CREATE TABLE #ProductTBL
(
ProductID int
)

INSERT #ProductTBL
SELECT DISTINCT
ProductID
FROM
Product
WHERE
ProductID IN (SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product)
ORDER BY ProductID

IF (SELECT COUNT(*) FROM #ProductTBL) = 1
BEGIN
SET @ProductDesc =
(SELECT [Description]
FROM
Product
WHERE ProductID in (SELECT ProductID FROM #ProductTBL))
END
ELSE
BEGIN
SET @ProductDesc = '*Multiple Products'
END

CREATE TABLE #ClientTBL
(
ClientID binary(8)
)

INSERT #ClientTBL
SELECT DISTINCT
ClientID = dlr.Company_Id
FROM
pivotal..Company dlr
WHERE
dlr.MA_Type IN (SELECT PARAM_VALUE
FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType )

IF (SELECT COUNT(*) FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType ) = 1
BEGIN
SET @ClientDesc =
(SELECT DISTINCT [MA_Type]
FROM
pivotal..Company
WHERE MA_Type in (SELECT PARAM_VALUE
FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType ))
END
ELSE
BEGIN
SET @ClientDesc = '*Multiple Clients'
END

--For Where Clause for SizeCashBucket
declare @LowSize int,
@HighSize int

SET @LowSize = case @SizeBucket when '<=1000' then 0
when 'All Sizes' then 0
else 1000
end

SET @HighSize = case @SizeBucket when '<=1000' then 999
when 'All Sizes' then 99999999
else 99999999
end
--For Where Clause for Maturity High and Low
declare @LowMaturity int,
@HighMaturity int

SET @LowMaturity = case @Maturity when '<=1' then 0
when 'All Maturity' then 0
when '>1 & <=3' then 1
when '>3& <=5' then 3
else 0
end

SET @HighMaturity = case @Maturity when '<=1' then 1
when 'All Maturity' then 99999999
when '>1 Year & <=3 Years' then 3
when '>3 Years & <=5 Years' then 5
else 99999999
end
--create temp table to store DealerCnt, ResponseCnt, Maturity
SELECT
ProductID,
Product,
SizeOrder,
SizeBucket,
DealerCnt,
ResponseCnt,
IsBuy ,
Maturity ,
ListID,
DealerBucket,
CAST(AVG(convert(float,DlrCnt)) AS decimal (10,2)),
CAST(AVG(convert(float, RspCntWithLevel )) AS decimal (10,2))
FROM

(SELECT DISTINCT
ProductID = iql.ProductID,
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = @SizeBucket,
DealerCnt = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
/*AvgDlrCnt = CAST(convert(float, COUNT(DISTINCT res.CPPivotalCompanyID)) AS decimal (10,2)),
AvgRspCntWithLevel = CAST(convert(float, (COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END))) AS decimal (10,2)),*/
IsBuy = @IsBuy,
Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,
ListID = inq.InquiryListID,
DealerBucket = CASE
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) >=1 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=5 THEN '>=1 & <=5 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 5 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=10 THEN '>=6 & <=10 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 10 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=15 THEN '>=11 & <=15 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 15 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=20 THEN '>=16 & <=20 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 20 THEN '>20 Dealers'
END

INTO #ResponseCnt
FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

LEFT JOIN
Trade trd
ON
iql.MISInquiryID = trd.MISInquiryID
and iql.LegSequence = trd.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
iql.ProductID = prd.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
rsl.MISResponseID = res.MISResponseID
and rsl.MISInquiryID = iql.MISInquiryID
and rsl.LegSequence = iql.LegSequence
INNER JOIN
#ClientTBL clnttbl

ON
clnttbl.ClientID = trd.PPivotalCompanyID
INNER JOIN
pivotal..Company dlr
ON
dlr.Company_Id = res.CPPivotalCompanyID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and inq.InquiryType = 'F'
and trd.IsBuy = CASE @IsBuy WHEN 'Bid' THEN 1 WHEN 'Offer' THEN 0 ELSE trd.IsBuy END
and iql.USDSize between @LowSize and @HighSize
and convert(int,datepart(yy,Datediff(yy,inq.InquiryDate,iss.Maturity))) between @LowMaturity and @HighMaturity

GROUP BY
iql.ProductID,
prd.Description,
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,
inq.InquiryListID) t

Thanks for your help

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-28 : 10:44:12

I also read abot CTE on the msdn site but did not quite understand how to apply it in the above query.

What do you think I am doing wrong in the query using Derived table?

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 13:19:13

you cant dump your results to a temporary table inside the inner query. can you please let us know why you want to do that? If you really want to store those results why dont you populate temporary table first and then use it in the further queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-28 : 14:56:30

I need the temporary table to join to another query as below. I did just that but it did more of cross joins and my groupings got messed up.

SELECT
ProductID = iql.ProductID,
AvgDlrCnt = CAST(AVG(convert(float, rescnt.DealerCnt)) AS decimal (10,2)),
AvgRspCntWithLevel = CAST(AVG(convert(float, rescnt.ResponseCnt)) AS decimal (10,2)),
DealerBucket = rescnt.DealerBucket

INTO #AvgDealerResponse
FROM
Inquiry inq
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
INNER JOIN
#ResponseCnt rescnt
--ON
--inq.MISInquiryID = rescnt.MISInquiryID
ON
iql.ProductID = rescnt.ProductID

INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID
WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate

GROUP BY
iql.ProductID,
prd.Description,
rescnt.DealerBucket

order by
dlr.ProductID
select * from #AvgDealerResponse

My results and groupings look correct hen I select from the three temp tables that I populated. However my final select does more of cross joins and messes up the grouping.

--Final Select
SELECT
dlr.Product AS Product,
--dlr.SizeBucket AS SizeBucket,
avgdlr.DealerBucket AS Dealer,
dlr.Maturity As Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount AS HitRatioCount,
TradeVolume,
dlr.HitRatioVolume AS HitRatioVolume,
avgdlr.AvgDlrCnt AS AvgDealerCount,
avgdlr.AvgRspCntWithLevel AS AvgResponseCount

from
#DealerTemp dlr

INNER JOIN
#AvgDealerResponse avgdlr
ON
avgdlr.ProductID = dlr.ProductID

order by dlr.ProductID
GO

Hence I was trying to eliminate the creation of the third temp table,#AvgDealerResponse and finding a way to have the three columns: avgdlr.DealerBucket, avgdlr.AvgDlrCnt ,avgdlr.AvgRspCntWithLevel that I need in the #ResponseCnt temp table.
columns for DealerBucket,avgdlr.AvgDlrCnt ,avgdlr.AvgRspCntWithLevel .

There are 4 ranges for DealerBucket and 5 ranges for Maturity. There are 9 products. There are two sizes <= 1 MM AND > 1MM.

My final result set looks something like below and the join to the #AvgDealerResponse table messes up the resultset. The last two columns keep repeating. There should be about 180 rows and I get 265. I believe the cross join is messing up the grouping and returning many rows.

Dealer Maturity Inquiry Inquiry Trade HitRatio Trade Hit Avg Avg
Count Vol Count Count Vol RatioVol Dealer Response
Count Count
>=6 & <=10 Dealers <= 1 Year 5012 1200112 3693 73.7 885085.00 73.8 8.59 3.16
>=6 & <=10 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 8.59 3.16
>=6 & <=10 Dealers > 1 Year <= 3 Years 10508 2265461 7176 68.3 1499833 66.2 8.59 3.16

If this does not make sense I can paste the entire code. I definitely need the

Thanks for your help

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-28 : 15:04:43

I am trying to group by in the final select to see if this fixes the groupings but I am not sure if this will work.

Thanks for your help

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-28 : 22:22:22

I just added DISTINCT in the SELECT for the first two temp table inserts. I also added the group by in the final select. Now the grouping makes sense

I have 2 size buckets <= 1MM and > 1MM. So when I select All Sizes as a parameter, I shoyld get results for each size bucket. Then I have 4 buckets for Maturity and 5 buckets for Dealers. So I shold technically get 4*2*5 = 40 rows for each product for whicj the Avg Dealer Count and Avg Response Count values repeat.

Please let me know if you knwo of a better way or whether the way I have coded is not ugly.

Thanks for all your help.

Below is the query FYI..

DECLARE
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'
@ClientType varchar(50), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@Product varchar(50), -- '100' map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100), --<=1MM >1MM
@Maturity varchar(100)

SET @StartDate = '20100101'
SET @EndDate = '20100201'
SET @ClientType = '154246'
SET @Product = '154247'
SET @ListID = 'All Inquiries'
SET @IsBuy = 'Bid/Offer'
SET @SizeBucket = 'All Sizes'
SET @Maturity = '<= 1 Year'

/*IF OBJECT_ID('dbo.rptAvgDealerResponse') IS NOT NULL
BEGIN
DROP PROC dbo.rptAvgDealerResponse
IF OBJECT_ID('dbo.rptAvgDealerResponse') IS NOT NULL
PRINT '<<< FAILED DROPPING PROC dbo.rptAvgDealerResponse >>>'
ELSE
PRINT '<<< DROPPED PROC dbo.rptAvgDealerResponse >>>'
END
GO
CREATE PROCEDURE [dbo].[rptAvgDealerResponse]
(
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'
@ClientType varchar(50), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@Product varchar(50), -- '100' map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100), --<=1MM >1MM
--@DealerBucket varchar(100), -->=1 & <=5 Dealers >=6 & <=10 Dealers >=11 & <=15 Dealers >=16 & <=20 Dealers >20 Dealers
@Maturity varchar(100) --<= 1 Year

)

AS
SET NOCOUNT ON

if exists
(select * from sysobjects where id = object_id(N'[#rptSalesReportByClientPlan]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)*/

DECLARE
@EndOfEndDate datetime,
@ProductDesc varchar(500),
@ProductID int,
@ProductCount int,
@ClientDesc varchar(100),
@DealerBucket varchar(100)

EXECUTE
misReportDate
@StartDate = @StartDate OUTPUT,
@EndDate = @EndDate OUTPUT,
@EndOfEndDate = @EndOfEndDate OUTPUT

if object_id ('tempdb..#ResponseCnt') is not null
drop table #ResponseCnt
if object_id ('tempdb..#DealerTemp') is not null
drop table #DealerTemp
if object_id ('tempdb..#AvgDealerResponse') is not null
drop table #AvgDealerResponse
if object_id ('tempdb..#ProductTBL') is not null
drop table #ProductTBL
if object_id ('tempdb..#ClientTBL') is not null
drop table #ClientTBL
if object_id ('tempdb..#ProductName') is not null
drop table #ProductName
if object_id ('tempdb..#ClientTypeName') is not null
drop table #ClientTypeName

--use this for Product and Client and Size display on the reporting tool
CREATE TABLE #ProductTBL
(
ProductID int
)

INSERT #ProductTBL
SELECT DISTINCT
ProductID
FROM
Product
WHERE
ProductID IN (SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product)
ORDER BY ProductID

IF (SELECT COUNT(*) FROM #ProductTBL) = 1
BEGIN
SET @ProductDesc =
(SELECT [Description]
FROM
Product
WHERE ProductID in (SELECT ProductID FROM #ProductTBL))
END
ELSE
BEGIN
SET @ProductDesc = '*Multiple Products'
END

CREATE TABLE #ClientTBL
(
ClientID binary(8)
)

INSERT #ClientTBL
SELECT DISTINCT
ClientID = dlr.Company_Id
FROM
pivotal..Company dlr
WHERE
dlr.MA_Type IN (SELECT PARAM_VALUE
FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType )

IF (SELECT COUNT(*) FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType ) = 1
BEGIN
SET @ClientDesc =
(SELECT DISTINCT [MA_Type]
FROM
pivotal..Company
WHERE MA_Type in (SELECT PARAM_VALUE
FROM MARPTGEN_PARAMLIST
WHERE convert(varchar(50),PARAMLIST_KEY) = @ClientType ))
END
ELSE
BEGIN
SET @ClientDesc = '*Multiple Clients'
END

--For Where Clause for SizeCashBucket
declare @LowSize int,
@HighSize int

SET @LowSize = case @SizeBucket when '<=1000' then 0
when 'All Sizes' then 0
else 1000
end

SET @HighSize = case @SizeBucket when '<=1000' then 999
when 'All Sizes' then 99999999
else 99999999
end
--For Where Clause for Maturity High and Low
declare @LowMaturity int,
@HighMaturity int

SET @LowMaturity = case @Maturity when '<=1' then 0
when 'All Maturity' then 0
when '>1 & <=3' then 1
when '>3& <=5' then 3
else 0
end

SET @HighMaturity = case @Maturity when '<=1' then 1
when 'All Maturity' then 99999999
when '>1 Year & <=3 Years' then 3
when '>3 Years & <=5 Years' then 5
else 99999999
end
--create temp table to store DealerCnt, ResponseCnt, Maturity
SELECT DISTINCT
ProductID = iql.ProductID,
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = @SizeBucket,
DealerCnt = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
IsBuy = @IsBuy,
Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,
ListID = inq.InquiryListID,
DealerBucket = CASE
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) >=1 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=5 THEN '>=1 & <=5 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 5 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=10 THEN '>=6 & <=10 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 10 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=15 THEN '>=11 & <=15 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 15 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=20 THEN '>=16 & <=20 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 20 THEN '>20 Dealers'
END

INTO #ResponseCnt
FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

LEFT JOIN
Trade trd
ON
iql.MISInquiryID = trd.MISInquiryID
and iql.LegSequence = trd.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
iql.ProductID = prd.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
rsl.MISResponseID = res.MISResponseID
and rsl.MISInquiryID = iql.MISInquiryID
and rsl.LegSequence = iql.LegSequence
INNER JOIN
#ClientTBL clnttbl

ON
clnttbl.ClientID = trd.PPivotalCompanyID
INNER JOIN
pivotal..Company dlr
ON
dlr.Company_Id = res.CPPivotalCompanyID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and inq.InquiryType = 'F'
and trd.IsBuy = CASE @IsBuy WHEN 'Bid' THEN 1 WHEN 'Offer' THEN 0 ELSE trd.IsBuy END
and iql.USDSize between @LowSize and @HighSize
and convert(int,datepart(yy,Datediff(yy,inq.InquiryDate,iss.Maturity))) between @LowMaturity and @HighMaturity

GROUP BY
iql.ProductID,
prd.Description,
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END,
inq.InquiryListID

--For Single and a List selection from combo box in the GI Reporting tool
If @ListID = 'Single Inquiry'
begin
Delete from #ResponseCnt
where ListID is not null
end
else if @ListID = 'List'
begin
Delete from #ResponseCnt
where ListID is null
end
select * from #ResponseCnt

--Inquiry Count, Trade Count, HitRatioCount etc should also be based on the Dealer Range and Maturity Range
--using the one tested on 3/18/10

SELECT
DISTINCT
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = @SizeBucket,
InquiryCount = COUNT(DISTINCT inq.MISInquiryID),
InquiryVolume = SUM(iql.USDSize),
TradeCount = COUNT(DISTINCT trd.MISInquiryID),
TradeVolume = SUM(IsNull(trd.USDSize,0)),
ProductID = iql.ProductID,
HitRatioCount = cast(COUNT(DISTINCT trd.MISInquiryID)* 100.0/COUNT(DISTINCT inq.MISInquiryID) as decimal(10,1)),
HitRatioVolume = cast(SUM(IsNull(trd.USDSize,0))* 100.0/SUM(iql.USDSize) as decimal(10,1)),
Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END

INTO
#DealerTemp

FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
iql.MISInquiryID = inq.MISInquiryID

LEFT JOIN
Trade trd
ON
trd.MISInquiryID = iql.MISInquiryID
and trd.LegSequence = iql.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and iql.USDSize between @LowSize and @HighSize
and convert(int,datepart(yy,Datediff(yy,inq.InquiryDate,iss.Maturity))) between @LowMaturity and @HighMaturity

GROUP BY
iql.ProductID,
prd.Description,
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,

CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END

ORDER BY iql.ProductID
select * from #DealerTemp
--Temp table to store Dealer and Response Count
SELECT
DISTINCT
ProductID = iql.ProductID,
AvgDlrCnt = CAST(AVG(convert(float, rescnt.DealerCnt)) AS decimal (10,2)),
AvgRspCntWithLevel = CAST(AVG(convert(float, rescnt.ResponseCnt)) AS decimal (10,2)),
DealerBucket = rescnt.DealerBucket

INTO #AvgDealerResponse
FROM
Inquiry inq
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
INNER JOIN
#ResponseCnt rescnt
--ON
--inq.MISInquiryID = rescnt.MISInquiryID
ON
iql.ProductID = rescnt.ProductID

INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID
WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate

GROUP BY
iql.ProductID,
prd.Description,
rescnt.DealerBucket

order by
iql.ProductID
select * from #AvgDealerResponse
--Create a temp table to store the Products in a table for the report footer
CREATE TABLE #ProductName
(
i int IDENTITY (1, 1) NOT NULL ,
ProductName varchar(4000)

)
INSERT #ProductName
SELECT
ShortName
FROM Product
WHERE ProductID IN(SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product)

DECLARE @ColumnValues varchar(8000)
SELECT @ColumnValues = ISNULL(@ColumnValues + ',', '') + ProductName
FROM #ProductName

--For Client Type display on report footer

create table #ClientTypeName
(
ClientTypeName varchar(50)
)
insert into #ClientTypeName
SELECT
DISTINCT dlr.MA_Type
FROM pivotal..Company dlr
WHERE MA_Type IN(SELECT PARAM_VALUE
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @ClientType)
DECLARE @ClientValues varchar(8000)
SELECT @ClientValues = ISNULL(@ClientValues + ', ', '') + ClientTypeName
FROM #ClientTypeName

--Final Select
SELECT
-- DISTINCT
dlr.Product AS Product,
dlr.SizeBucket AS SizeBucket,
avgdlr.DealerBucket AS Dealer,
dlr.Maturity As Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount AS HitRatioCount,
TradeVolume,
dlr.HitRatioVolume AS HitRatioVolume,
avgdlr.AvgDlrCnt AS AvgDealerCount,
avgdlr.AvgRspCntWithLevel AS AvgResponseCount,
@ColumnValues AS ProductName,
@ClientDesc AS TypeofClient,
@ProductDesc As ProductType,
@ClientValues AS ClientTypeName

from
#DealerTemp dlr

INNER JOIN
#AvgDealerResponse avgdlr
ON
avgdlr.ProductID = dlr.ProductID

group by
avgdlr.AvgDlrCnt,
avgdlr.AvgRspCntWithLevel,
avgdlr.DealerBucket,
dlr.Maturity,
dlr.ProductID,
dlr.Product,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount,
TradeVolume,
dlr.HitRatioVolume,
dlr.SizeBucket

order by dlr.ProductID
GO

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-29 : 10:35:25

I have one more question.

If I have the final results as below: How can I group the remaining columns results in such a way that I get one row of results for each different Dealer, Avg Dealer and Avg Response? Will a Where clause help filter some of the dups I am seeing for some columns?

My final query is :

--Final Select
SELECT
DISTINCT
dlr.ProductID,
dlr.Product AS Product,
dlr.SizeBucket AS SizeBucket,
avgdlr.DealerBucket AS Dealer,
dlr.Maturity As Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount AS HitRatioCount,
TradeVolume,
dlr.HitRatioVolume AS HitRatioVolume,
avgdlr.AvgDlrCnt AS AvgDealerCount,
avgdlr.AvgRspCntWithLevel AS AvgResponseCount,
@ColumnValues AS ProductName,
@ClientDesc AS TypeofClient,
@ProductDesc As ProductType,
@ClientValues AS ClientTypeName

from
#DealerTemp dlr

INNER JOIN
#AvgDealerResponse avgdlr
ON
avgdlr.ProductID = dlr.ProductID

group by
dlr.ProductID,
dlr.Product,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount,
TradeVolume,
dlr.HitRatioVolume,
avgdlr.AvgDlrCnt,
avgdlr.AvgRspCntWithLevel,
avgdlr.DealerBucket,
dlr.Maturity,
dlr.SizeBucket

order by dlr.ProductID

GO

ProductID Product SizeBucket Dealer Maturity InquiryCount Inquiry Volume Trade Count Hit Ratio Trade Volume Hit Ratio Volume Avg Dealer Avg Response

1 High Grade All Sizes >=1 & <=5 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 3.38 2.04
1 High Grade All Sizes >=6 & <=10 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 8.59 3.16
1 High Grade All Sizes >=11 & <=15 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 13.66 5.2
1 High Grade All Sizes >=16 & <=20 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 17.51 6.45
1 High Grade All Sizes >20 Dealers > 3 Years <= 5 Years 988 2700966 736 74.5 1845220 68.3 29.81 8.14

Thanks for your help again

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-29 : 11:16:50

Can I add a filter like below?

WHERE
dlr.InquiryCount IN(select distinct InquiryCount from #DealerTemp)

The table #DealerTemp has distinct rows of data. But when I join to the 3AvgDealerResponse in the final select I get dups for some columns.

Product Size Size Inquiry Inquiry Trade
Order Count Volume Count Volume ID HitR HitR Maturity
Bucket
High Grade 2 All Sizes 988 2700966.00 736 1845220.00 1 74.5 68.3 > 3 Years <= 5 Years
High Grade 1 All Sizes 8401 1685571.00 6083 1152298.00 1 72.4 68.4 > 3 Years <= 5 Years

Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources