December 9, 2008 at 7:13 am
Hello Experts out there,
How are you doing? The scripts provided below are adding columns into one new column then Sum at the bottom of the new column, and eliminated the time. When I run this scripts, it gave me an error message and that message is:
"Column 'CTE.TotalCleared' is invalid in the select list because it's not containedd in either an aggregate function or the Group By Clause."
Here is my sample data for the script below
DEClearedDate SomaticClearedDate PsycClearedDate
1/1/2008 1/1/2008 1/2/2008
1/4/2008 1/2/2008 1/6/2008
1/9/2008 1/7/2008 1/15/2008
1/16/2008 1/17/2008 1/16/2008
TotalCleared ClearedDate
2 01/01/2008
2 01/02/2008
1 01/04/2008
1 01/06/2008
1 01/07/2008
1 01/09/2008
1 01/15/2008
2 01/16/2008
1 01/17/2008
--------
12
WITH CTE AS
(SELECT ISNULL(a.DECleared, 0) + ISNULL(b.SomaticCleared, 0) + ISNULL(c.PsycCleared, 0) AS TotalCleared,
COALESCE(a.DEClearedDate, b.SomaticClearedDate, c.PsycClearedDate) AS ClearedDate
FROM dbo.DECleared AS a FULL OUTER JOIN dbo.SomaticCleared AS b
ON b.SomaticClearedDate = a.DEClearedDate
FULL OUTER JOIN dbo.PsycCleared as c ON
c.PsycClearedDate = b.SomaticClearedDate)
SELECT sum(TotalCleared), ClearedDate
FROM (SELECT TotalCleared, CONVERT(char(10), ClearedDate, 101) AS ClearedDate
FROM CTE
GROUP BY ClearedDate) AS DATA
GROUP BY ClearedDate WITH ROLLUP
Thank You Very Much
Joe
December 9, 2008 at 7:20 am
this part of the code
SELECT TotalCleared, CONVERT(char(10), ClearedDate, 101) AS ClearedDate
FROM CTE
GROUP BY ClearedDate
contains a Group By that does not include the column TotalCleared.
you need to perform an aggregate funciton on this column, eg. SUM, MAX. AVG
or add this column to your Group By
December 9, 2008 at 7:21 am
The problem is in the bolded and underlined line of code:
SELECT sum(TotalCleared), ClearedDate
FROM (SELECT TotalCleared, CONVERT(char(10), ClearedDate, 101) AS ClearedDate
FROM CTE
GROUP BY ClearedDate) AS DATA
GROUP BY ClearedDate WITH ROLLUP
Since you are not doing any aggregation there you should be able to remove the entire line in question.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2008 at 7:53 am
Hi Jack
Thank You Very Much for your wonderful helps
Gob Bless you Bro.
Joe
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply