How to sum a running number in sql

Question

I have a running total in a query that I need to sum.
the query displays the total number of airplanes flying from a specific mfr and model

1. how to i sum(totalnumberflying) and display it on the last raw ?
2. how do i order by MFR and MODEL
3. how do i display instead of a list in 3 columns horizontally ?

SELECT 1 as sorted,
 Count(*) As 'TotalNumberFlying'
  
  ,[AircraftReg].[dbo].[MASTER].MfrMdlCode
  ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MFR]
  ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MODEL]
 

 FROM [AircraftReg].[dbo].[MASTER],[AircraftReg].[dbo].[FixedWingSingleEngine]
 Where [AircraftReg].[dbo].[MASTER].MfrMdlCode =  [AircraftReg].[dbo].[FixedWingSingleEngine].[CODE] and [AircraftReg].[dbo].[MASTER].State='CO'
 
 Group by 
   [AircraftReg].[dbo].[MASTER].MfrMdlCode
  ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MFR]
  ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MODEL] 

  order by TotalNumberFlying

Answer 1

Hi @Elad R

how to i sum(totalnumberflying) and display it on the last raw ? how do i order by MFR and MODEL

Try this:

;WITH CTE AS
(
SELECT 1 as sorted
      ,Count(*) As 'TotalNumberFlying'
      ,[AircraftReg].[dbo].[MASTER].MfrMdlCode
      ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MFR]
      ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MODEL]
FROM [AircraftReg].[dbo].[MASTER],[AircraftReg].[dbo].[FixedWingSingleEngine]
Where [AircraftReg].[dbo].[MASTER].MfrMdlCode =  [AircraftReg].[dbo].[FixedWingSingleEngine].[CODE] and [AircraftReg].[dbo].[MASTER].State='CO'
Group by [AircraftReg].[dbo].[MASTER].MfrMdlCode
        ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MFR]
        ,[AircraftReg].[dbo].[FixedWingSingleEngine].[MODEL] 
)
SELECT MFR,MODEL,SUM(TotalNumberFlying) AS TotalNumberFlying
FROM CTE
GROUP BY GROUPING SETS((MFR,MODEL),())
ORDER BY GROUPING(MFR),GROUPING(MODEL);

how do i display instead of a list in 3 columns horizontally ?

Don't know what you mean. Could you please post more details?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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.