Problem with expressions in Group By

[Nevermoor]

I am trying to finish the following sql view and make it so that the two columns named sorter and sorter2 can be combined using ISNULL() so that I can sort by the column.

SELECT     TOP 100 PERCENT COUNT(dbo.tblMetrics.metricID) AS projCount, dbo.tblMetrics.metricID, ISNULL(dbo.tblQuestInfo.category, 
                      ISNULL(dbo.tblEnvScorecard.title, 'UNUSED')) AS category, dbo.tblMetrics.title, dbo.tblEnvScorecard.sorter, 
                      dbo.tblQuestInfo.sortOrder / 100 AS sorter2
FROM         dbo.tblMetrics LEFT OUTER JOIN
                      dbo.tblEnvScorecard ON ',' + dbo.tblEnvScorecard.metric1 + ',' + dbo.tblEnvScorecard.metric2 + ',' LIKE '%,' + CAST(dbo.tblMetrics.metricID AS varchar(8))
                       + ',%' LEFT OUTER JOIN
                      dbo.tblQuestInfo ON dbo.tblMetrics.metricID = dbo.tblQuestInfo.metricID
GROUP BY dbo.tblMetrics.metricID, dbo.tblQuestInfo.category, dbo.tblMetrics.title, dbo.tblEnvScorecard.title, dbo.tblEnvScorecard.title, 
                      dbo.tblEnvScorecard.sorter, dbo.tblQuestInfo.sortOrder / 100
ORDER BY dbo.tblQuestInfo.category

This should be easy, i.e. use ISNULL(dbo.tblEnvScorecard.sorter, dbo.tblQuestInfo.sortOrder/100), but doing that gives me the following error:

Column 'dbo.tblQuestInfo.sortOrder' is invalid in the select list because it is not contained in either an aggregate function or the group by clause

I need to divide by 100 because a metric can be associated with many questions, but all the questions will be in the same section, as defined by the digit in the hundreds place.

Any ideas/questions?

 

[SQLBill]

The way you have it in your code:

 dbo.tblEnvScorecard.sorter, dbo.tblQuestInfo.sortOrder / 100 AS sorter2

is NOT the same as:

ISNULL(dbo.tblEnvScorecard.sorter, dbo.tblQuestInfo.sortOrder/100),

In your script it's not used with ISNULL nor is it in parenthesis.

-SQLBill

 

[Nevermoor]

right, the working code I posted puts dbo.tblEnvScorecard.sortin one column and dbo.tblQuestInfo.sortOrder / 100 in another.

The problem is that I need to sort by the combination of the two (one is always a value, the other is always null).

How can I use ISNULL in that statement to combine them into one column that I can then sort by?

Thank you,
-Nm

 

[Nevermoor]

For clarification, here is some sample data returned by the SQL in my original Post

projCount metricID	category						title					sorter	sorter2
1		13		Energy And Atmosphere			Grid-Supplied Electricity	(NULL)	2

1		14		Energy And Atmosphere Section	Reduced electricity use		2	(NULL)

I need it to be:

projCount metricID	category						title					sorter
1		13		Energy And Atmosphere			Grid-Supplied Electricity	2

1		14		Energy And Atmosphere Section	Reduced electricity use		2