| Author |
 Topic  |
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
 Posted - 11/13/2012 : 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 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_ID
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/13/2012 : 11:38:29
|
You need to group by all the columns that you aren't aggregating
Add
GROUP BY
CYCLE.CY_CYCLE ,CYCLE.CY_COMMENT, etc.
to the end of your query
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 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_ID
GROUP 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
Yak Posting Veteran
89 Posts |
Posted - 11/13/2012 : 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 Report
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_ID
GROUP 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/13/2012 : 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_COMMENT
To see how long that field really is, and use that number, plus some padding, in place of xx
SELECT convert(varchar(xx) ,CYCLE.CY_COMMENT) as CY_Comment
...
FROM ...
GROUP BY ...,CYCLE.CY_COMMENT,..
Jim
Everyday I learn something that somebody else already knew |
|
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
Posted - 11/13/2012 : 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)),
|
|
|
| |
Topic |
|
|
|
Using Count in Select
