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.
Author |
Topic |
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 11:32:40
|
The following SQL gives error "Column 'CYCLE.CY_CYCLE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."Does this mean I can't use COUNT in the SELECT? The insertion of the line that that uses COUNT caused the error. If I can't use COUNT, how can I accomplish a count?SELECT-- Test Set FieldsCYCLE.CY_CYCLE AS "Test Set",CYCLE.CY_COMMENT AS "Description",CYCLE.CY_USER_01 AS "Functional Area",CYCLE.CY_OPEN_DATE AS "Open Date",CYCLE.CY_CLOSE_DATE AS "Close Date",CYCLE.CY_REQUEST_ID AS "ITG Request ID",CYCLE.CY_STATUS AS "Status",CYCLE.CY_PINNED_BASELINE AS "Baseline",CYCLE.CY_CYCLE_ID AS "Cycle ID",CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",CYCLE.CY_VTS AS "Modified",-- Test Instance FieldsCOUNT(TESTCYCL.TC_TEST_ID) AS "Number of Tests"FROM TESTCYCLINNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 11:38:29
|
You need to group by all the columns that you aren't aggregatingAddGROUP BYCYCLE.CY_CYCLE ,CYCLE.CY_COMMENT, etc.to the end of your queryJimEveryday I learn something that somebody else already knew |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-13 : 11:40:15
|
With very few exceptions, any columns in the select list must either be inside an aggregate function, or must be listed in the group by clause. You can use Windowing functions as an alternative. The two queries below show the two options; see if either will fit your needs--- 2--------------------SELECT -- Test Set Fields CYCLE.CY_CYCLE AS "Test Set", CYCLE.CY_COMMENT AS "Description", CYCLE.CY_USER_01 AS "Functional Area", CYCLE.CY_OPEN_DATE AS "Open Date", CYCLE.CY_CLOSE_DATE AS "Close Date", CYCLE.CY_REQUEST_ID AS "ITG Request ID", CYCLE.CY_STATUS AS "Status", CYCLE.CY_PINNED_BASELINE AS "Baseline", CYCLE.CY_CYCLE_ID AS "Cycle ID", CYCLE.CY_ASSIGN_RCYC AS "Target Cycle", CYCLE.CY_VTS AS "Modified", -- Test Instance Fields COUNT(TESTCYCL.TC_TEST_ID) AS "Number of Tests"FROM TESTCYCL INNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_IDGROUP BY CYCLE.CY_CYCLE , CYCLE.CY_COMMENT , CYCLE.CY_USER_01 , CYCLE.CY_OPEN_DATE , CYCLE.CY_CLOSE_DATE , CYCLE.CY_REQUEST_ID , CYCLE.CY_STATUS , CYCLE.CY_PINNED_BASELINE , CYCLE.CY_CYCLE_ID , CYCLE.CY_ASSIGN_RCYC , CYCLE.CY_VTS --- 2--------------------SELECT -- Test Set Fields CYCLE.CY_CYCLE AS "Test Set", CYCLE.CY_COMMENT AS "Description", CYCLE.CY_USER_01 AS "Functional Area", CYCLE.CY_OPEN_DATE AS "Open Date", CYCLE.CY_CLOSE_DATE AS "Close Date", CYCLE.CY_REQUEST_ID AS "ITG Request ID", CYCLE.CY_STATUS AS "Status", CYCLE.CY_PINNED_BASELINE AS "Baseline", CYCLE.CY_CYCLE_ID AS "Cycle ID", CYCLE.CY_ASSIGN_RCYC AS "Target Cycle", CYCLE.CY_VTS AS "Modified", -- Test Instance Fields COUNT(TESTCYCL.TC_TEST_ID) OVER() AS "Number of Tests"FROM TESTCYCL INNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 11:48:52
|
OK. That works. However, I had to comment out one column from the SELECT and from the GROUP BY ... CYCLE.CY_COMMENT ... it's a memo field and apparently can't be used in this context. So does that leave me with no way to select CYCLE.CY_COMMENT? If so, it may not be a big issue but I will be asked about it. Here's the new SQL that works but has CYCLE.CY_COMMENT commented out:-- Test Set ReportSELECT-- Test Set FieldsCYCLE.CY_CYCLE AS "Test Set",--CYCLE.CY_COMMENT AS "Description",CYCLE.CY_USER_01 AS "Functional Area",CYCLE.CY_OPEN_DATE AS "Open Date",CYCLE.CY_CLOSE_DATE AS "Close Date",CYCLE.CY_REQUEST_ID AS "ITG Request ID",CYCLE.CY_STATUS AS "Status",CYCLE.CY_PINNED_BASELINE AS "Baseline",CYCLE.CY_CYCLE_ID AS "Cycle ID",CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",CYCLE.CY_VTS AS "Modified",-- Test Instance FieldsCOUNT(TESTCYCL.TC_TEST_ID) AS "Number of Tests"FROM TESTCYCLINNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_IDGROUP BY CYCLE.CY_CYCLE, --CYCLE.CY_COMMENT,CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID,CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 11:55:23
|
You can try and convert the comment field to a varchar.Do SELECT max(len(convert(varchar(max),cy_comment))) FROM CYCLE.CY_COMMENTTo see how long that field really is, and use that number, plus some padding, in place of xxSELECT convert(varchar(xx) ,CYCLE.CY_COMMENT) as CY_Comment...FROM ...GROUP BY ...,CYCLE.CY_COMMENT,..JimEveryday I learn something that somebody else already knew |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 12:54:32
|
Thanks. Had to do two things:In the SELECT:convert(nvarchar(MAX),CYCLE.CY_COMMENT) AS "Description", In the GROUP BY:CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), |
|
|
|
|
|
|
|