Fixing group by - SQL Server Forums

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)

Fixing group by

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-18 : 08:45:36

Hi

I have the below final select

SELECT DISTINCT
dlr.ProductID AS ProductID,
dlr.Product AS Product,
SizeBucket,
avgdlrrsc.DealerBucket AS DealerBucket,
rescnt.Maturity As Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
htr.HitRatioCount AS HitRatioCount,
TradeVolume,
htr.HitRatioVolume AS HitRatioVolume,
avgdlrrsc.AvgDlrCnt AS AvgDealerCount,
avgdlrrsc.AvgRspCntWithLevel AS AvgResponseCount

from
#DealerTemp dlr

LEFT JOIN
#ResponseCnt rescnt
ON
dlr.ProductID = rescnt.ProductID

INNER JOIN
#HitRatio htr
ON
dlr.ProductID = htr.ProductID
INNER JOIN
#AvgDealerResponse avgdlrrsc
ON
dlr.ProductID = avgdlrrsc.ProductID
group by
rescnt.Maturity,
avgdlrrsc.DealerBucket,
avgdlrrsc.AvgDlrCnt,
avgdlrrsc.AvgRspCntWithLevel,
dlr.ProductID ,
dlr.Product ,
SizeBucket,
InquiryCount,
InquiryVolume,
TradeCount,
htr.HitRatioCount,
TradeVolume,
htr.HitRatioVolume

order by
--SizeOrder,
dlr.ProductID
--DealerBucket

My results appear as

ID Product Size Dealer Maturity
1 High Grade <=1MM >20 Dealers <= 1 Year
1 High Grade <=1MM >20 Dealers <= 1 Year
1 High Grade <=1MM >20 Dealers > 5 Years
1 High Grade <=1MM >20 Dealers > 5 Years
1 High Grade <=1MM >20 Dealers >1 & <= 3 Years
1 High Grade <=1MM >20 Dealers >1 & <= 3 Years
1 High Grade <=1MM >20 Dealers >3 & <= 5 Years
1 High Grade <=1MM >20 Dealers >3 & <= 5 Years
I need the a row for each distinct Maturity and Dealer for example. Whether or not I use the group by, I get the same result set.

How do I get the result as
ID Product Size Dealer Maturity
1 High Grade <=1MM >20 Dealers <= 1 Year
1 High Grade <=1MM >20 Dealers > 5 Years
1 High Grade <=1MM >20 Dealers >1 & <= 3 Years
1 High Grade <=1MM >20 Dealers >3 & <= 5 Years

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:11:46

whats the purpose of other columns on select and group by then? remove them if you want one record per
ID Product Size Dealer Maturity group. only include required columns in select/groupby

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources