SQL Server - rollup with non-aggregated figure

Question

I am calculating a % completeness figure for a number of sites, with each site having a designated 'Base' figure. I've used the code below to work out each site's individual completeness but I'm struggling to get an overall sum of the 'Base' figure:

select isnull(Site,'Total') Site, max(base) Base,
        round(count(submissionID)/max(base)*100,2) DataCompleteness
from PRIcache
        left join hsite on hsite=site and hyear=2013
where network='sussex' and year(arvd)=2015
group by site with rollup

I've used max as I needed to include the field in an aggregate function but as result the total row displays the max base figure from all the sites instead of the sum of the base figures. For example:

Site 1 base=214
Site 2 base=114
Total base=214

Is there a way to add the base figure into my select statement so it will totalled up? Thanks.

SQL fiddle link with example: http://sqlfiddle.com/#!3/ddf38/1

Is it as simple as replace your MAX with SUM ? I'm guessing not, or this question wouldn't be here. Could you make an [sqlfiddle](http://sqlfiddle.com/) of it? — LDMJoe, Jul 09 '15 at 15:05
When I replace it with sum it sums the base figure by the count of the submissionIDs, I just want to pull through the static figure. Trying to set up a sql fiddle but I'm getting an error at the moment, will keep trying. — GullitsMullet, Jul 10 '15 at 10:08
Here's a basic SQL fiddle example - thanks: http://sqlfiddle.com/#!3/ddf38/1 — GullitsMullet, Jul 10 '15 at 10:23
`WITH ROLLUP` is deprecated and should not be used for new development — Mark Sinkinson, Jul 10 '15 at 12:50

LDMJoe · Answer 1 · 2015-07-10T14:08:21.537

I think I see the issue now. You need to do the aggregate function at a different level than where you join to your base.

Take a look at the results that this...

SELECT
  base.site AS [Site],
  ISNULL(DerivedTotalsBySite.[Site Total], 0) AS [Site Total],
  base.base AS [Site Base],
  ROUND(CONVERT(real, ISNULL(DerivedTotalsBySite.[Site Total], 0)) / CONVERT(real, base.base) * 100.0, 2) AS [% Complete]
FROM
  base
  LEFT OUTER JOIN
  (
  select 
    siteid, 
    sum(SubmissionNumber) AS [Site Total]
  from 
    Submissions
  group by 
    siteid 
   ) DerivedTotalsBySite ON DerivedTotalsBySite.siteid = base.site

...produces in your sqlfiddle. Is that the desired result?

SQL Server - rollup with non-aggregated figure

1 Answers1