How to display the sum of a computed column is sql

Question

I have a computed column  TotalNumberFlying
i Need at the bottom of the query to display the total of the column.
any ideas ?


SELECT 
 Count(*) As 'TotalNumberFlying'

  ,[AircraftReg].[dbo].[MASTER].MfrMdlCode
  ,[AircraftReg].[dbo].[FixedWingMultiEngine].[MFR]
  ,[AircraftReg].[dbo].[FixedWingMultiEngine].[MODEL]
 

 FROM [AircraftReg].[dbo].[MASTER],[AircraftReg].[dbo].[FixedWingMultiEngine]
 Where [AircraftReg].[dbo].[MASTER].MfrMdlCode =  [AircraftReg].[dbo].[FixedWingMultiEngine].[CODE]
 
 Group by 
   [AircraftReg].[dbo].[MASTER].MfrMdlCode
  ,[AircraftReg].[dbo].[FixedWingMultiEngine].[MFR]
  ,[AircraftReg].[dbo].[FixedWingMultiEngine].[MODEL] 



  
 order by TotalNumberFlying

Answer 1

I've taken the liberty to rewrite your query to use table aliases to make it easier to read. I've also rewritten it to use the JOIN operator, since this is how we normally write joins in SQL Server. Furthermore, I've removed brackets not needed, again to improve legibility.

Finally, I have changed the GROUP BY and ORDER BY clause to meet the requirement in your question.

SELECT Count(*) AS TotalNumberFlying, M.MfrMdlCode, F.MFR, F.MODEL
FROM   AircraftReg.dbo.MASTER AS M
JOIN   AircraftReg.dbo.FixedWingMultiEngine AS F ON M.MfrMdlCode = F.CODE
GROUP  BY GROUPING SETS ((M.MfrMdlCode, F.MFR, F.MODEL), ())
ORDER  BY grouping(F.MFR), TotalNumberFlying

Answer 2

Hi @Eran Dorot

I wrote the following code using CTE and union all, you can give it a try.

;with CTE as(
  SELECT Count(*) As 'TotalNumberFlying',A.MfrMdlCode,B.[MFR],B.[MODEL]
  FROM [AircraftReg].[dbo].[MASTER] as A inner join 
      [AircraftReg].[dbo].[FixedWingMultiEngine] as B
      on A.MfrMdlCode = B.[CODE]
  Group by A.MfrMdlCode,B.[MFR],B.[MODEL]
  order by TotalNumberFlying)
select * from CTE
union all
select sum(TotalNumberFlying),null,null,null from CTE;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.