SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using Count in Select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/13/2012 :  11:32:40  Show Profile  Reply with Quote
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
2869 Posts

Posted - 11/13/2012 :  11:38:29  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  11:40:15  Show Profile  Reply with Quote
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
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/13/2012 :  11:48:52  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  11:55:23  Show Profile  Reply with Quote
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
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/13/2012 :  12:54:32  Show Profile  Reply with Quote
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)),


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000