SQlite3 - Select showing all cols with sub total after each category group
 

Hi,

In MySql there is a way to do this without too much hassle. See attachment Target.txt.

The best I can produce in SQlite3 is: See attachment Actual.txt.

Can someone tell me what query I can use to accomplish Target.txt format?

Thanks in advance R.

If I understand your question, you could do that with your existing query by simply changing what you select for those columns in the subtotal query of the UNION, something like this:

I have not actually run this in SQLite but it should support aliasing in the results set. Because you are ordering by category you will need to retain it to keep the total with the items it is total of, perhaps adding an ordering clause to keep the empty description row after non-empty rows.

Maybe
also untested.

Thanks folks,

We're getting there.

I tried this query and got: see attachment getttingThere.png

I don't understand why the alias 'Sub Total' didn't work. However, just having the first two fields blank helps.
Any other ideas?

Sorry. The alias sets the column heading.
Try Still not tested...not even at my desktop right now.

That isn't how alias works, it would be just the reverse of that, like this:

The number and name of the columns must be the same in both queries, so the UNION of each of those results has the same columns. When you alias category as "Sub Total" you are creating a fifth column named "Sub Total" which has the contents of category as its value, but it doesn't show because the first query in the UNION does not include such a column.

However, if you (correctly) alias "Sub Total" as category, the string "Sub Total" will indeed appear in the category column, but the order by clause will put it elsewhere in the row ordering, as would an empty category field in that row. Hence my original comment that you would need to add some ordering criteria to keep it in place, perhaps adding a separate column for maintaining order.

In general, when I need more complex subtotals and totals for some result set for which ROLLUP (not supported by SQLite as far as I know) is not appropriate, I would select the categories and their subtotals into a temporary table with suitable ordering and an auto_increment column, then select the final result with grand total from the temporary table, ordered by the auto_increment column which need not be selected in the final result set.

However, using what you have, something like this might work for you:

That should work by adding a fifth column, Total, empty for each item but the total amount in the grouped result rows, and last in each group because of the ordering clause. Just in case that turns out to be first in each group change the ordering by using DESC.

You attach 2 files so why not .dump the table?
then we can try it out.

Sorry, great idea,

see attached SQL file showSQL.txt

Then this should produce something like you want, including a grand total row:

Note that this is all done using the tip I suggested originally about use of additional ordering clauses to put the subtotals and totals in the proper place. In this query the ORDER BY operates on the UNION of all the queries, i.e. after all the rows have been assembled.

Here is the result set I get (I did this in mysql with suitable substitutions of concat operator and table/column names).

Running your query with copy & paste to avoid typos got this result from sqlite:

I don't understand the underlined bold pieces of the query. Is this documented somewhere that I could read about?

Your result is exactly what I am looking for. Nice job. Now have to get working in SQlite.

The problem with the concat operator may be that I used double quotes in my query. That is acceptable for MySQL, but apparently not for SQLite:

Try that and let me know if it works.

As for the order by statement, a quick look at the docs inidcates SQLite may not allow compound expressions in the order by terms, only column names. My order by uses a descending boolean value as the first ordering criteria (category != 'GRAND TOTAL'), so all other rows evaluate to 1, while the last row evaluates to zero.

If SQLite does not allow that, then see if you can work out another way to do the same thing. Hint: A purpose-made ordering column which you can de-select with an enclosing SELECT clause.

Almost there. All the data is there so I could mamipulate it with PHP so Grand Total is at the bottom.

Reckon this could be what you need, maybe, if your brain doesn't explode :)

https://sqlite.org/lang_with.html

edit: the recursive bit

Thanks all, for your time in helping me!!!!

R