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

pizzipie · 12-12-2019, 01:24 PM

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.

astrogeek · 12-12-2019, 02:01 PM

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:

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT date, "" as description, category, SUM(amount) FROM rdata GROUP BY category ORDER BY category

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.

scasey · 12-12-2019, 06:41 PM

Maybe

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT " ", " " , category as "Sub Total", SUM(amount) FROM rdata GROUP BY category ORDER BY category

also untested.

pizzipie · 12-12-2019, 09:06 PM

Thanks folks,

We're getting there.

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

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT " ", " " , category as "Sub Total", SUM(amount) FROM rdata GROUP BY category ORDER BY category

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

scasey · 12-12-2019, 09:53 PM

Sorry. The alias sets the column heading.
Try

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT " ", " " , "Sub Total", SUM(amount) FROM rdata GROUP BY category ORDER BY category

Still not tested...not even at my desktop right now.

astrogeek · 12-12-2019, 09:58 PM

Quote:

Originally Posted by pizzipie

Thanks folks,

We're getting there.

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

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT " ", " " , category as "Sub Total", SUM(amount) FROM rdata GROUP BY category ORDER BY category

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

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

Code:

SELECT date, description, category, amount FROM rdata
UNION ALL
SELECT " ", " " , "Sub Total" as category, SUM(amount) FROM rdata GROUP BY category ORDER BY category

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:

Code:

SELECT date, description, category, amount, "" as Total FROM rdata
UNION ALL
SELECT " ", "", category, "Sub Total", SUM(amount) as Total FROM rdata GROUP BY category ORDER BY category, Total

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.

bigearsbilly · 12-13-2019, 06:38 AM

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

pizzipie · 12-13-2019, 01:15 PM

Sorry, great idea,

see attached SQL file showSQL.txt

astrogeek · 12-13-2019, 03:17 PM

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

Code:

SELECT date, description, category, amount 
    FROM rdata
UNION ALL
SELECT " ", "", category||" Sub Total" as category, SUM(amount) as amount 
    FROM rdata GROUP BY category
UNION ALL
SELECT " ", "", 'GRAND TOTAL' as category, SUM(amount) as amount 
    FROM rdata 
ORDER BY category!='GRAND TOTAL' DESC, category;

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).

Code:

MariaDB [test]> \. query2.sql
+------------+---------------------------------------+--------------------------+----------+
| datex      | description                           | category                 | amount   |
+------------+---------------------------------------+--------------------------+----------+
| 04/03/2019 | Kitchen Sink And Faucet               | appliances               |   438.50 |
| 04/01/2019 | Kitchen Fixtures Stove - Ect          | appliances               |   862.82 |
| 05/17/2019 | Kitchen Lights                        | appliances               |   113.67 |
| 05/03/2019 | Washer Dryer                          | appliances               |  1102.10 |
| 04/21/2019 | Refrigerator And BR Light             | appliances               |  1567.55 |
|            |                                       | appliances Sub Total     |  4084.64 |
| 04/26/2019 | House Cleanup                         | cleaning                 |   130.00 |
|            |                                       | cleaning Sub Total       |   130.00 |
| 11/19/2018 | Gas In Kitchen – Off-  Rm Lr Heater   | demo                     |    99.00 |
| 11/19/2018 | Remove Sinks And Toilets – Baths      | demo                     |   206.85 |
| 11/12/2018 | Remove Wallpaper                      | demo                     |   650.00 |
|            |                                       | demo Sub Total           |   955.85 |
| 08/16/2019 | Outdoor Lights                        | fixtures                 |    88.59 |
| 04/20/2019 | Tub Enclosures                        | fixtures                 |   372.80 |
|            |                                       | fixtures Sub Total       |   461.39 |
| 07/08/2019 | Laminate Flooring                     | floor-covering           | 10973.00 |
| 04/27/2019 | Living Room Rugs                      | floor-covering           |   375.37 |
| 04/13/2019 | Kitchen Rugs                          | floor-covering           |    81.61 |
|            |                                       | floor-covering Sub Total | 11429.98 |
| 07/03/2019 | Guestroom And Master Br               | furniture                |   487.49 |
|            |                                       | furniture Sub Total      |   487.49 |
| 08/16/2019 | Front Door Lock Set                   | hardware                 |   134.51 |
|            |                                       | hardware Sub Total       |   134.51 |
| 05/03/2019 | Window Coverings                      | interior                 |  1300.00 |
| 06/04/2019 | Window Coverings                      | interior                 |  1370.70 |
|            |                                       | interior Sub Total       |  2670.70 |
| 07/19/2019 | Sprinklers On/Tested R&R Spray Units  | irrigation               |   310.00 |
|            |                                       | irrigation Sub Total     |   310.00 |
| 06/17/2019 | Furnish And Install Cedar Mulch       | landscape                |   450.00 |
| 06/17/2019 | Furnish And Install Cedar Mulch       | landscape                |   300.00 |
| 05/08/2019 | Landscape                             | landscape                |   220.00 |
| 04/20/2019 | Clean Up Landscaping From Winter      | landscape                |   400.00 |
| 04/20/2019 | Landscape                             | landscape                |   400.00 |
| 10/07/2018 | Landscape – Rmv Trees – Misc          | landscape                |   410.00 |
| 06/24/2019 | Remove Vegetation At Elec Trans       | landscape                |   526.00 |
| 06/22/2019 | Furnish And Install Cedar Mulch       | landscape                |   500.00 |
|            |                                       | landscape Sub Total      |  3206.00 |
| 09/23/2019 | Garage Door                           | maint                    |   445.60 |
| 08/22/2019 | Garage Door                           | maint                    |    83.19 |
| 08/23/2019 | House Cleanup                         | maint                    |   100.00 |
| 04/25/2019 | House Cleanup                         | maint                    |   130.00 |
|            |                                       | maint Sub Total          |   758.79 |
| 08/21/2019 | TV Cabinet Materials                  | materials                |   110.09 |
|            |                                       | materials Sub Total      |   110.09 |
| 01/07/2019 | Misc                                  | misc                     |     8.63 |
| 05/23/2019 | Misc                                  | misc                     |    63.88 |
| 05/04/2019 | Misc                                  | misc                     |    22.29 |
| 02/09/2019 | Misc                                  | misc                     |     5.65 |
| 01/12/2019 | Misc                                  | misc                     |    25.55 |
| 01/11/2019 | Misc                                  | misc                     |     3.05 |
|            |                                       | misc Sub Total           |   129.05 |
|            |                                       | GRAND TOTAL              | 24868.49 |
+------------+---------------------------------------+--------------------------+----------+
52 rows in set, 1 warning (0.00 sec)

pizzipie · 12-13-2019, 06:52 PM

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

Quote:

1st ORDER BY term does not match any column in the result set: SELECT date, description, category, amount
FROM show <== note: I created a new small database with a table called show for simplicity
UNION ALL
SELECT " ", "", category||" Sub Total" as category, SUM(amount) as amount
FROM show GROUP BY category
UNION ALL
SELECT " ", "", 'GRAND TOTAL' as category, SUM(amount) as amount
FROM show
ORDER BY category!='GRAND TOTAL' DESC, category;

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.

astrogeek · 12-13-2019, 08:38 PM

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:

Quote:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.

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.

pizzipie · 12-14-2019, 05:10 PM

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

Quote:

Sat 14 Dec 2019 15:43:32

CREATE TABLE `show` (
`Id` INTEGER PRIMARY KEY AUTOINCREMENT,
`Date` TEXT NOT NULL,
`Description` TEXT NOT NULL,
`Category` TEXT NOT NULL,
`Amount` REAL
);

SELECT date, description, category, amount FROM show
UNION ALL
SELECT " ", " ", category || " Sub Total" as category, SUM(amount) as amount FROM show GROUP BY category
UNION ALL
SELECT " ", " ", 'GRAND TOTAL' as category, SUM(amount) as amount FROM show ORDER BY category

Date Description Category Amount
---------- ----------- ----------- ----------
GRAND TOTAL 24868.49
04/03/2019 Kitchen Sin appliances 438.5
04/21/2019 Refrigerato appliances 1567.55
05/03/2019 Washer Drye appliances 1102.1
05/17/2019 Kitchen Lig appliances 113.67
04/01/2019 Kitchen Fix appliances 862.82
appliances 4084.64
04/26/2019 House Clean cleaning 130.0
cleaning 130.0
11/12/2018 Remove Wall demo 650.0
11/19/2018 Remove Sink demo 206.85
11/19/2018 Gas In Kitc demo 99.0
demo Sub 955.85 <------ DB browser puts out Sub Total[/B]
04/20/2019 Tub Enclosu fixtures 372.8 apparently .column produces short column and the rest is truncated
08/16/2019 Outdoor Lig fixtures 88.59
fixtures 461.39
04/13/2019 Kitchen Rug floor-cover 81.61
04/27/2019 Living Room floor-cover 375.37
07/08/2019 Laminate Fl floor-cover 10973.0
floor-cover 11429.98
07/03/2019 Guestroom A furniture 487.49
furniture 487.49
08/16/2019 Front Door hardware 134.51
hardware 134.51
05/03/2019 Window Cove interior 1300.0
06/04/2019 Window Cove interior 1370.7
interior 2670.7
07/19/2019 Sprinklers irrigation 310.0
irrigation 310.0 <------------
10/07/2018 Landscape – landscape 410.0
04/20/2019 Landscape landscape 400.0
04/20/2019 Clean Up La landscape 400.0
05/08/2019 Landscape landscape 220.0
06/17/2019 Furnish And landscape 300.0
06/17/2019 Furnish And landscape 450.0
06/22/2019 Furnish And landscape 500.0
06/24/2019 Remove Vege landscape 526.0
landscape 3206.0 <----------
04/25/2019 House Clean maint 130.0
08/23/2019 House Clean maint 100.0
08/22/2019 Garage Door maint 83.19
09/23/2019 Garage Door maint 445.6
maint Sub 758.79
08/21/2019 TV Cabinet materials 110.09
materials 110.09 < ------------
01/07/2019 Misc misc 8.63
01/11/2019 Misc misc 3.05
01/12/2019 Misc misc 25.55
02/09/2019 Misc misc 5.65
05/04/2019 Misc misc 22.29
05/23/2019 Misc misc 63.88
misc Sub 129.05

bigearsbilly · 12-16-2019, 07:36 AM

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

https://sqlite.org/lang_with.html

edit: the recursive bit

pizzipie · 12-16-2019, 10:27 AM

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

R