Итог и сумма двух столбцов в одном запросе

Вы можете разделить выборки в одном запросе и объединить результаты, используя join или union. Для первого ожидаемого результата вы можете запросить, как показано ниже.

select sq1.count, sq1.ItemID, sq2.ItemCode, sq2.count, sq2.Location from (
  select S1.ItemID, count(*)  as count
  from Store S1
  group by S1.ItemID
) sq1
inner join (
  select S2.ItemID, I.ItemCode, Count(*), S2.Location from Store S2 
  inner join Item I
  on S2.ItemId = I.ItemId
  group by S2.ItemID, I.ItemCode, S2.Location
) sq2
on sq1.ItemID = sq2.ItemID
order by sq2.ItemID, sq2.ItemCode;

Для второго результата вы можете использовать этот запрос

select * from (
  select S.ItemID, I.ItemCode, Count(*), S.Location from Store S 
  inner join Item I
  on S.ItemId = I.ItemId
  group by S.ItemID, I.ItemCode, S.Location
  union
  select  S2.ItemID, 'Total'  as itemcode, Count(*), null
  from Store S2
  group by S2.ItemID
) sq
order by sq.ItemID, sq.ItemCode;

Вы можете использовать предложение ROLLUP:

    select I.ItemID,  case when S.Location is null then 'Total' else I.ItemCode end as ItemCode, count(*) as count,  S.Location
    from #Store S
         inner join
         #Item I 
         on S.ItemId=I.ItemID 
    group by  I.ItemID,I.ItemCode, rollup(S.Location)
    order by I.ItemId, S.Location NULLS LAST

Но вы должны убедиться, что S.Location не будет иметь нулевых значений (потому что я использовал его для пометки строки промежуточного итога).

Честно говоря, промежуточные и общие итоги и т. д. лучше обрабатываются на уровне представления (инструмент отчетности, форма и т. д.), поскольку механизмы SQL имеют ограниченные возможности для работы с множеством вариантов отображения, которые могут запросить пользователи.

Следующий запрос использует GROUPING SETS для достижения предпочтительных результатов ОП:

SELECT
  s.itemid,
  COALESCE(i.itemcode, 'Total') AS itemcode,
  COUNT(i.itemid),
  COUNT(s.location),
  s.location
FROM
  store s
  JOIN item i ON s.itemid = i.itemid
GROUP BY
  GROUPING sets ((s.itemid, i.itemcode, s.location), (s.itemid))
ORDER BY
  s.itemid,
  s.location;