MySQL Query Grouping by Month

Question

I have a MySQL query which outputs data like this:

This is my current query that I'm using to generate the output.

SELECT *
FROM (SELECT staff.firstname AS stafffirstname
             , staff.surname AS staffsurname
             , transactions.total
             , transactions.description
             , transactions.datetime
             , transactions.ID
             , (@runningtotal := @runningtotal + transactions.total) AS balance
      FROM transactions
        LEFT JOIN staff ON transactions.createdbyID = staff.ID
      WHERE transactions.tenancyID = ?
      ORDER BY transactions.datetime
               , transactions.ID) FullResults
ORDER BY FullResults.datetime
         , FullResults.ID 
LIMIT ? , ?;

So it's doing a full query to build the running balance, and then selects a subset to output. The table I'm using supports paging.

How can I take the data and group all the subitems together by month? IE, all the November Standing Charges should only have one line in the table. And all the November Usage Charges should only have one line.

UPDATE

This is my query so far with help from JustLukas.

SELECT
  FullResults.*
FROM
  (SELECT
      staff.firstname AS stafffirstname,
      staff.surname AS staffsurname,
      transactions.total,
      transactions.description,
      transactions.datetime,
      transactions.ID,
      (@runningtotal := @runningtotal + transactions.total) AS balance,
      Concat(CONVERT(IfNull(transactions.meterID, ''), CHAR), ";", CONVERT(transactions.typeID, CHAR), ";",
      Left(MonthName(transactions.datetime), 3), CONVERT(Year(transactions.datetime), CHAR)) AS GroupingField
    FROM
      transactions
      LEFT JOIN staff ON transactions.createdbyID = staff.ID
    WHERE
      transactions.tenancyID = 2631
    ORDER BY
      transactions.datetime,
      transactions.ID) FullResults
GROUP BY
  FullResults.GroupingField

How do I get the SUM(transaction.total) for each group? At the moment, it's only showing me the total for the first row in each group.

Thanks Dan

"How can I take the data and group all the subitems together by month?" `GROUP BY MONTH()` maybe.. — Raymond Nijland, Nov 23 '17 at 14:48

score 0 · Answer 1 · answered Nov 23 '17 at 14:54

0

Try using below code, trick is to convert the date to something groupable. Not sure what you meant in the part Where transactions.tenancyID = ?

select * from 
   (Select 
      staff.firstname As stafffirstname, 
      staff.surname As staffsurname, 
      transactions.total, 
      transactions.description, 
      left(Datename(month,transactions.datetime),3) + char(39) + right(Datename(year,transactions.datetime),2) as 'Period',
      transactions.ID, 
      (@runningtotal:= @runningtotal + transactions.total) as balance 
    From transactions Left Join staff On transactions.createdbyID = staff.ID 
    Where transactions.tenancyID = ? 
    Order By transactions.datetime, transactions.ID
   ) FullResults 
Group By 
  FullResults.Period,  
  transactions.description, 
  transactions.ID,
  FullResults.stafffirstname,
  FullResults.staffsurname

answered Nov 23 '17 at 14:54

JustLukas

13
5

The ? is replaced using parameterized queries. – Dan_J Nov 23 '17 at 14:56
You can adjust it how you see fit – JustLukas Nov 23 '17 at 14:57
Note that the Datename() function does not exist in Mysql. I had to use MONTHNAME(). And for MySQL, I need to use CONCAT() to combine the string to make the Period field. – Dan_J Nov 23 '17 at 15:40
When grouped, how do I get the SUM of transactions.total for each group? – Dan_J Nov 23 '17 at 15:41
Could you add to your post the result youre getting atm? – JustLukas Nov 25 '17 at 08:31

score 0 · Answer 2 · answered Nov 23 '17 at 15:06

0

try with group_concat, it returns one line per group element

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

How to use GROUP_CONCAT in a CONCAT in MySQL

answered Nov 23 '17 at 15:06

ditch

92
9

MySQL Query Grouping by Month

2 Answers2