Query a MySQL Database and Return a HTML Table Report using Flask/jQuery

Hello. I have two tables: `accounts` and `journal`. Accounts is: +----------+---------+------------------+--------+ | account | level | report | nature | +----------+---------+------------------+--------+ | salary | level 4 | income statement | 1 | | rent | level 4 | income statement | 0 | | expense1 | level 4 | income statement | 0 | | expense2 | level 4 | income statement | 0 | +----------+---------+------------------+--------+ Journal is: +------------+---------+--------+---------+ | date | credit | debit | value | +------------+---------+--------+---------+ | 11-10-2017 | cash | stocks | $ 100 | | 10-09-2017 | savings | cash | $ 500 | | 10-09-2017 | revenue | cash | $ 2.000 | +------------+---------+--------+---------+ I came up with the following query: ``` SELECT date_format(date_cash, '%b-%y'), account, ROUND(sum(value), 2) FROM ( SELECT [login to view URL] AS report, date_cash, debit AS account, SUM(CASE WHEN [login to view URL] = 0 THEN value ELSE -value END) AS value FROM journal JOIN accounts AS act_debit ON [login to view URL] = [login to view URL] GROUP BY debit, YEAR(date_cash), MONTH(date_cash) UNION SELECT [login to view URL] AS report, date_cash, credit AS account, SUM(CASE WHEN [login to view URL] = 0 THEN value ELSE -value END) AS value FROM journal JOIN accounts AS act_credit ON [login to view URL] = [login to view URL] GROUP BY credit, YEAR(date_cash), MONTH(date_cash) ) AS table WHERE (report="Income Statement") GROUP BY account, YEAR(date_cash), MONTH(date_cash) ORDER BY date_cash ASC ``` Which gives me the following table: ``` +----------+---------+-------+ | date | account | value | +----------+---------+-------+ | Jan-2017 | salary | 2.500 | | Jan-2017 | rent | 500 | | Feb-2017 | salary | 2.500 | | Feb-2017 | rent | 500 | +----------+---------+-------+ ``` This is a "stacked" version of an Income Statement, with all months stacked (and repeated) in the date column, and all accounts stacked (and repeated) in the value accounts column. It definitely does not seem like the optimal code - it runs at about 150ms in Sequel Pro and has too many lines and repeated (similar) arguments - but it does work for flow account. Even though it does not return NULL values (which are important in this case), they come up as soon as I transfer this table to a DataFrame in Python Pandas, and manipulate the query to get a Pivot Table just as the desired output from the question - accounts that have never been used do not appear though, which is something that I still have to work on - tried `RIGHT JOIN` in both statements to get all values from the accounts table, but that did not work. Python code is: ``` df = pandas.read_sql(query, cnx) report_is = pandas.pivot_table(df, values='Value', index='account', columns='data') ``` The "duplicated" `SELECT` is there because some accounts are credit by nature (credit increases the account), and some accounts are debit by nature (debit increases the account) - edited the question to reflect the change that had to be done in my DB. Probably there's a better way to do this. Now, how do I get this into a HTML table with collapsable rows - Level 1 can collapse to show only the Level 1 total, or expand to show all acounts (which are child of the Level 1 parent) up to Level 4. [Example]([login to view URL])