Using Count in Select - 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

General SQL Server Forums

New to SQL Server Programming

Using Count in Select

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 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
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 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
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_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
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 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
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_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
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)),

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources