How to sum a running number in sql

Elad R 0 Reputation points
2023-05-23T16:24:02.51+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,312 questions
{count} votes

1 answer

Sort by: Most helpful
  1. LiHongMSFT-4306 25,651 Reputation points
    2023-05-24T06:35:12.6033333+00:00

    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.

    0 comments No comments