Inserting using 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)

Inserting using group by

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 13:51:08

Hi,

I get this error when i try to insert a column in a temp table:
Column '#Trade.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I add the trd.Product column to the Group By clause, I get anotehr error. But if I use ProductID instead I get the results. i want to be able to see the Product. Is this because the Product is a non numeric and I cannot group by the column? What am I missing?

Thanks

SELECT
InquiryDate = inq.InquiryDate,
InquiryID = inq.SystemInquiryID,
Client = cli.Company_Name,
CPLevel = MAX(trd.CPLevel),
DealerCount = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCount = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
ExecutedDealer = MAX(CASE WHEN rsl.State = 'T' THEN REPLACE(dlr.Short_Name_1, '_US', '') END),
ExecutedPrice = MAX(CASE WHEN rsl.State = 'T' THEN rsl.Price END),
ExecutedLevel = MAX(CASE WHEN rsl.State = 'T' THEN rsl.CPLevel END),
CoverLevel = MAX(CASE WHEN rsl.State = 'C' THEN rsl.CPLevel END),
CoverDealer = MAX(CASE WHEN rsl.State = 'C' THEN REPLACE(dlr.Short_Name_1, '_US', '') END),
TradeYear = trd.TradeYear,
TradeMonthName = trd.TradeMonthName,
TradeMonth = trd.TradeMonth,
Product = trd.Product

INTO
#Response
FROM
#Trade trd
INNER JOIN
Inquiry inq
ON
trd.MISInquiryID = inq.MISInquiryID
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
res.MISResponseID = rsl.MISResponseID
and iql.MISInquiryID = rsl.MISInquiryID
and iql.LegSequence = rsl.LegSequence
INNER JOIN
pivotal..Company dlr
ON
res.CPPivotalCompanyID = dlr.Company_Id
INNER JOIN
pivotal..Company cli
ON
trd.ClientID = cli.Company_Id
WHERE
inq.IsVolume = 1
and iql.IsVolume = 1
and InquiryType = 'F'
GROUP BY
trd.TradeYear,
trd.TradeMonth,
trd.TradeMonthName,
cli.Company_Name,
inq.InquiryDate,
inq.SystemInquiryID

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 13:53:56

Never mind. I got it. just selected trd.Product and grouped by trd.Product since the Product already exists in the temp table #Trade.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources