SQL Total of Column

Question

Hello I have a query where I am getting counts of records that are group by month (see example result below). What I would like SQL to do is have an additional Total row at the bottom which will be a sum of all the values in the "AccountID" column. I have highlighted the "Total" row where I would this addition to my current result set. Appreciate any help!!

Below is my current query I am using to gather the data:

  SELECT Distinct
  Month = MONTH(pv.VisitStartDateTime)
  ,AccountID_Count = count(pv.AccountID) 

   FROM  hpavisit pv With ( NOLOCK )
   left outer join HPat pt With ( NOLOCK )
   ON      pv.Pat_OID = pt.ObjectID
   and pt.isdeleted = 0
   Left Outer Join HCareUnit HCU With ( NOLOCK )
   on      HCU.ObjectID = pv.PatLocation_oid
   Left Outer Join HCareUnit HCU1 With ( NOLOCK )
   on      HCU1.ObjectID = pv.UnitContacted_oid

   WHERE pv.PatLocation_oid <> 0 and pv.PatLocation_oid is not null
   and pv.IsDeleted = 0    
   and pv.VisitStartDateTime > '08/31/2014 23:23:59'
   and pv.VisitStartDateTime < '03/01/2015'
   GROUP BY MONTH(pv.VisitStartDateTime)

Maybe a dupe of [Add a summary row with totals](http://stackoverflow.com/questions/17934318/add-a-summary-row-with-totals)? — Just Do It, Oct 02 '15 at 18:57

score 0 · Answer 1 · answered Oct 02 '15 at 20:46

On SQL2008 you can add sumary row

SELECT Distinct
  Month = MONTH(pv.VisitStartDateTime)
 ,AccountID_Count = count(pv.AccountID) 

   FROM  hpavisit pv With ( NOLOCK )
   left outer join HPat pt With ( NOLOCK )
   ON      pv.Pat_OID = pt.ObjectID
   and pt.isdeleted = 0
   Left Outer Join HCareUnit HCU With ( NOLOCK )
   on      HCU.ObjectID = pv.PatLocation_oid
   Left Outer Join HCareUnit HCU1 With ( NOLOCK )
   on      HCU1.ObjectID = pv.UnitContacted_oid

   WHERE pv.PatLocation_oid <> 0 and pv.PatLocation_oid is not null
   and pv.IsDeleted = 0    
   and pv.VisitStartDateTime > '08/31/2014 23:23:59'
   and pv.VisitStartDateTime < '03/01/2015'
   GROUP BY MONTH(pv.VisitStartDateTime)
   Union ALL
SELECT Distinct
  'TOTAL',
 ,AccountID_Count = count(pv.AccountID) 

   FROM  hpavisit pv With ( NOLOCK )
   left outer join HPat pt With ( NOLOCK )
   ON      pv.Pat_OID = pt.ObjectID
   and pt.isdeleted = 0
   Left Outer Join HCareUnit HCU With ( NOLOCK )
   on      HCU.ObjectID = pv.PatLocation_oid
   Left Outer Join HCareUnit HCU1 With ( NOLOCK )
   on      HCU1.ObjectID = pv.UnitContacted_oid

   WHERE pv.PatLocation_oid <> 0 and pv.PatLocation_oid is not null
   and pv.IsDeleted = 0    
   and pv.VisitStartDateTime > '08/31/2014 23:23:59'
   and pv.VisitStartDateTime < '03/01/2015'

On SQL20008R2 you can try:

SELECT Distinct
  Month = MONTH(pv.VisitStartDateTime)
 ,AccountID_Count = count(pv.AccountID) 

   FROM  hpavisit pv With ( NOLOCK )
   left outer join HPat pt With ( NOLOCK )
   ON      pv.Pat_OID = pt.ObjectID
   and pt.isdeleted = 0
   Left Outer Join HCareUnit HCU With ( NOLOCK )
   on      HCU.ObjectID = pv.PatLocation_oid
   Left Outer Join HCareUnit HCU1 With ( NOLOCK )
   on      HCU1.ObjectID = pv.UnitContacted_oid

   WHERE pv.PatLocation_oid <> 0 and pv.PatLocation_oid is not null
   and pv.IsDeleted = 0    
   and pv.VisitStartDateTime > '08/31/2014 23:23:59'
   and pv.VisitStartDateTime < '03/01/2015'
   GROUP GROUP BY GROUPING SETS( 
   (MONTH(pv.VisitStartDateTime),
   ())

SQL Total of Column

1 Answers1