Sum of totals grouped by month-CodePudding

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:-

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

I want it to give me a total for each month

CodePudding user response：

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)

CodePudding user response：

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.