x
Solved

# Sum All Rows

Posted on 2013-11-25
Medium Priority
261 Views
I have the following SP:

``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
``````

I've been tasked to get an overall total for each facility for each year of all organtypes. So, not broken apart. Can that be done within this code or will I need to create something separate?

Thanks.
0
Question by:LCNW
• 2
• 2

LVL 25

Accepted Solution

chaau earned 2000 total points
ID: 39676089
Yes, it is possible. What about the sorting? Are you going to sort it inside the program, or you want it in the stored procedure? This code is doing the totals you need without the sorting:
``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
UNION ALL
SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas
FROM CTE1
GROUP BY [Year],Facility
``````
And this one is with the proper sorting (but with an extra column at the back, that can be ignored)
``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')
SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas FROM
(SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas, 1 as SortOrder
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas, 3 as SortOrder
FROM CTE1
GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
UNION ALL
SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder
FROM CTE1
GROUP BY [Year],Facility) x
ORDER BY SortOrder, CrossClampedOn,[Year],Facility
``````
0

LVL 1

Author Comment

ID: 39676212
I'm getting a conversion error:

Conversion failed when converting date and/or time from character string.
0

LVL 25

Assisted Solution

chaau earned 2000 total points
ID: 39676219
OK then. I expected something like this:
Replace then this line in both of my examples
``````SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder
``````
to
``````SELECT NULL CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder
``````
You just need to know that when there is an empty "CrossClampedOn" column, it is the Overall total
0

LVL 1

Author Closing Comment

ID: 39676683
Thanks, this helped.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.