Menu
I have a table which has this schema
ItemID UserID Year IsPaid PaymentDate Amount 1 1 2009 0 2009-11-01 300 2 1 2009 0 2009-12-01 342 3 1 2010 0 2010-01-01 243 4 1 2010 0 2010-02-01 2543 5 1 2010 0 2010-03-01 475 I'm trying to get a query working which shows the totals for each month. So far I've tried DateDiff and nested selects, but neither gives me what I want. This is the closest I have I think:
DECLARE @start [datetime] = 2010/4/1; SELECT ItemID, IsPaid, (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4 AND UserID = 100) AS "Aug", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5 AND UserID = 100) AS "Sep", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6 AND UserID = 100) AS "Oct", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb", (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar" FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY WHERE UserID = 16178 But I just get nulls when I should be getting values. Am I missing something?
407
Group By Month and Year We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers. In the above query, if we use date_format function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically.
To select all entries from a particular month in MySQL, use the monthname() or month() function.
SELECT DATE_FORMAT(Due_Date , '%m') AS Month_Group FROM DemoGroupMonth GROUP BY MONTH(Due_Date) DESC; The succeeding output is produced which displays month grouped by implementing GROUP BY. As you can see the DATE column values are displayed in the result set by grouping with month and in descending order.
The SQL GROUP BY StatementThe GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount] FROM Payments GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120) ORDER BY [Month] You could also try:
SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount] FROM Payments GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate) ORDER BY Year, Month Restrict the dimension of the NVARCHAR to 7, supplied to CONVERT to show only "YYYY-MM"
SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount] FROM Payments GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120) ORDER BY [Month] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
