Query help to find total

• Guras

  SSCertifiable

  Points: 6518

  More actions

  September 15, 2014 at 7:32 am

  #296297

  select col1,count(*) from client1..table1 group by col1

  union

  select col1,count(*) from client2..table1 group by col1

  union

  select col1,count(*) from client3..table1 group by col1

  The results yields

  33915

  3405

  3412

  I am trying to get the following result but can't figure out how to get the total in the end.

  33915

  3405

  3412

  Total 22

  Any help would be greatly appreciated.

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  September 15, 2014 at 8:01 am

  #1745488

  declare @rollup table(

  id int,

  cnt int)

  insert into @rollup values(339,15),(340,5),(341,2)

  select isnull(cast(id as varchar(12)),'Total'),SUM(cnt) from @rollup

  group by rollup(id)

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 16, 2014 at 7:02 am

  #1745760

  A couple variations (these will get you the same query plan as what rxm posted).

  SELECT isnull(cast(id as varchar(12)),'Total'), sum(cnt)

  FROM @rollup

  GROUP BY id with rollup;

  or...

  -- since we're only grouping on one column

  SELECT isnull(cast(id as varchar(12)),'Total'), sum(cnt)

  FROM @rollup

  GROUP BY id with cube;

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001