December 5, 2008 at 9:22 am
Hi Everyone and Expert out there,
how can I add sum at the bottom of the column TotalCleared.
Here is my script and the display of my script:
WITH CTE AS
(SELECT ISNULL(a.DECleared, 0) + ISNULL(b.SomaticCleared, 0) AS TotalCleared,
ISNULL(a.DEClearedDate, b.SomaticClearedDate) AS CLearedDate
FROM dbo.DECleared AS a FULL OUTER JOIN
dbo.SomaticCleared AS b ON b.SomaticClearedDate = a.DEClearedDate)
SELECT TotalCleared, CONVERT(char(10), CLearedDate, 101) AS ClearedDate
FROM CTE
ORDER BY CLearedDate
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
1 01/16/2008
Wish to show the total(sum) at the end of the column "TotalCleared" is: 10
TotalCleared
2
2
1
1
1
1
1
1
--------
10
Thank You
December 5, 2008 at 9:37 am
Joe,
Once again... Please refer to the link in my signature for how to provide proper sample data. You'll get better and faster answers to your questions if you do so. When Bob helped you yesterday, he had to manufacture all of that stuff himself because you didn't provide adequate information.
December 5, 2008 at 10:08 am
and this is usually a client side thing to do, not a SQL calculation.
December 5, 2008 at 11:10 am
josephptran2002:
You can use "WITH ROLLUP" to produce the total line. Look it up in the SQL documentation.
WITH CTE AS
( SELECT ISNULL(a.DECleared, 0) + ISNULL(b.SomaticCleared, 0) AS TotalCleared,
ISNULL(a.DEClearedDate, b.SomaticClearedDate) AS CLearedDate
FROM dbo.DECleared AS a FULL OUTER JOIN
dbo.SomaticCleared AS b ON b.SomaticClearedDate = a.DEClearedDate)
SELECT sum(TotalCleared), ClearedDate
FROM
(SELECT TotalCleared, CONVERT(char(10), CLearedDate, 101) AS ClearedDate
FROM CTE
ORDER BY CLearedDate) as DATA
GROUP BY ClearedDate
WITH ROLLUP
December 5, 2008 at 12:51 pm
Hi Antonio,
First, Thank You Very Much for your wonderful helps. But somehow when I add one more column in script then it told me "ISNULL function needs two arguments" Do you know why?
WITH CTE AS
(SELECT ISNULL(a.DECleared, 0) + ISNULL(b.SomaticCleared, 0) + ISNULL(c.PsycCleared, 0) AS TotalCleared,
ISNULL(a.DEClearedDate, b.SomaticClearedDate, c.PsycClearedDate) AS ClearedDate
FROM dbo.DECleared as a FULL OUTER JOIN
dbo.SomaticCleared as b FULL OUTER JOIN
dbo.PsycCleared as c ON c.PsycClearedDate=b.SomaticClearedDate=a.DEClearedDate)
SELECT sum(TotalCleared), ClearedDate
FROM
(SELECT TotalCleared, CONVERT(char(10), CLearedDate, 101) AS ClearedDate
FROM CTE
ORDER BY CLearedDate) as DATA
GROUP BY ClearedDate
WITH ROLLUP
Thank You Very Much Antonio
December 5, 2008 at 1:23 pm
ISNULL(a.DEClearedDate, b.SomaticClearedDate, c.PsycClearedDate) AS ClearedDate
isnull can only accept 2 parameters. you can use coalesce() or multiple isnull()s.
COALESCE(a.DEClearedDate, b.SomaticClearedDate, c.PsycClearedDate) AS ClearedDate
ISNULL(a.DEClearedDate, ISNULL(b.SomaticClearedDate, c.PsycClearedDate)) AS ClearedDate
December 5, 2008 at 1:25 pm
Hi Antonio
Thank so much
Very Respectful
Joe
December 5, 2008 at 1:31 pm
Hi Antonio
I fixed the ISNULL and replaced it by COALESCE. But it gives me a small syntax error and that errors are:
Error in ON Clause near '='
Error in ON Clause near ','
Error Message: Incorrect syntax near '='
Do you know Why?
Thanks Antonio
You are genius
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 FULL OUTER JOIN dbo.PsycCleared as c ON
c.PsycClearedDate = b.SomaticClearedDate = a.DEClearedDate)
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply