'Sum of totals grouped by month

I've been out of the dev world for a few years so forgive me if this is a pretty basic question but I have an app that logs bookings for holiday accomodation. I want to produce a report detailing how much income per month a user gets.

My query thus far is as so:-

SELECT SUM(int_ToOwner) AS TotalIncome, 
       DateName(m,dtm_StartDate) AS BookingMonth 
FROM tbl_Bookings 
WHERE dtm_StartDate > '2021-12-31'
GROUP BY DatePart(m,dtm_StartDate), int_ToOwner, dtm_StartDate

But that produces the result below. I want it to give me a total for each month instead.

TotalIncome BookingMonth
553.00 January
849.00 January
885.00 February
1236.00 February
1239.00 February
896.00 March
927.00 March
940.00 March
959.00 March
971.00 March
1167.00 April
1255.00 April
1500.00 April
2461.00 April
1131.00 May
1172.00 May
1275.00 May
2647.00 May
1466.00 June
1480.00 June
1496.00 June
1899.00 June
2167.00 June
1881.00 July
4990.00 July
4991.00 July
2134.00 August
4162.00 August
4883.00 August
5329.00 August
1430.00 September
1630.00 October
1130.00 November
sqlsql-server


Solution 1:[1]

You almost have it but you are also grouping by int_ToOwner and you have the dtm_StartDate twice.

Try:

SELECT SUM(int_ToOwner) AS TotalIncome, DateName(m,dtm_StartDate) AS BookingMonth
FROM tbl_Bookings 
WHERE dtm_StartDate > '2021-12-31' 
GROUP BY DatePart(m,dtm_StartDate)

Solution 2:[2]

A little re-format:

SELECT SUM(int_ToOwner)          AS TotalIncome
,      DateName(m,dtm_StartDate) AS BookingMonth 
FROM   tbl_Bookings 
WHERE  dtm_StartDate > '2021-12-31' 
GROUP BY DatePart(m,dtm_StartDate)
,      int_ToOwner
,      dtm_StartDate

Your GROUP BY tells the database to create groups for data with equal values of

  • DatePart(m,dtm_StartDate)
  • int_ToOwner
  • dtm_StartDate

Then SELECT asks for each group the

  • calculated SUM of int_ToOwner
  • DateName(m,dtm_StartDate)

You should search your solution in grouping the correct attributes.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Crick3t
Solution 2 hetOrakel

Related Questions