Grouping and Summing in SQL-CodePudding

Good day

I would like to find out how I would Sort and group the following data by grouping the Account and summing the grandTotal of the Account per Month.

This is my current Select statement:

SELECT 
    tbl_AccountLedger.ledgerName
    ,tbl_SalesMaster.date
    , tbl_SalesMaster.grandTotal

  FROM tbl_SalesMaster
  INNER JOIN tbl_AccountLedger ON tbl_SalesMaster.ledgerId =tbl_AccountLedger.ledgerId

Here is wat my select statement is bringing back

Now I need to sort this data by summing the grand total for each month for a legerName

CodePudding user response：

You can use the below query on SQL 2012 or higher version. I will suggest please read grouping in SQL.

SELECT 
tbl_AccountLedger.ledgerName
,DATEFROMPARTS(YEAR(tbl_SalesMaster.date),MONTH(tbl_SalesMaster.date),1) AS Month
, SUM(tbl_SalesMaster.grandTotal) AS TotalGrandTotal
FROM tbl_SalesMaster
  INNER JOIN tbl_AccountLedger ON tbl_SalesMaster.ledgerId =tbl_AccountLedger.ledgerId
GROUP BY tbl_AccountLedger.ledgerName, DATEFROMPARTS(YEAR(tbl_SalesMaster.date),MONTH(tbl_SalesMaster.date),1)