Django Cursor

MySQL Queries for Grouping Data By Year by Eric on February 1, 2020

A big request we had in designing our new system was an easier way for users to get data from the database.  With the large amounts of data that we use across dozens of tables it can be slow going to use the Django methods.  Instead they have given the ability to use and write raw SQL and use that result how you please.  You can read about using cursors here, I may do a post in the future about them but today I want to focus on the SQL we wrote and the problems we encountered. The requested query was to get a list of customers, how much business we did with them, and break that up by year.  The year could be inputted by the user so it needed to be dynamic enough to scale.  The first way we tried was to create a temporary table for every year needed.  Each table was created from a SELECT statement that was specified for the office as well as the year.  Below is the SQL command for one of the offices and years but you can imagine we copied this code, almost verbatim, but changed the ...