Problem: All the data I want to summarize is stored in a single database table.
Using SQL I need to group the data into different levels of summarization.
For example:
In the database table we have this:
Code Description Amount
--------------------------------------
200 Windows 1000 <This amount is empty in the database - it is calculated by the program>
210 Window Frames 200 <This amount is empty in the database - it is calculated by the program>
211 Bottom Frames 50
212 Top Frames 50
213 Side Frames 100
220 Glass 700 <This amount is empty in the database - it is calculated by the program>
221 Inner Glaze 250
222 Outer Glaze 250
223 Tinting 100
230 Misc 100 <This amount is empty in the database - it is calculated by the program>
231 Rubber Seals 40
232 Handles 60
300 Doors 500 <This amount is empty in the database - it is calculated by the program>
310 Door Frames 250 <This amount is empty in the database - it is calculated by the program>
311 Top Frame 100
312 Bottom Frame 100
313 Side Frames 50
320 Door 200 <This amount is empty in the database - it is calculated by the program>
321 Basic Door 175
322 Venier 25
330 Misc 50 <This amount is empty in the database - it is calculated by the program>
331 Door Knobs 50
So 220, 230, 310, 320 etc are just titles with empty amounts. The amounts will be the subtotals of all the
following codes (ie 221, 222, 223 etc and 321, 322, 323 etc)
Codes like 200 and 300 are also empty except for a description - the amount is the subtotal of the 220, 230, 240 subtotals.
Hopefully this can bee seen in the list above - i've tried to group things into blocks. The top of each block is the subtotal
line.
In order to select only the Level 1 subtotals (eg 200, 300, 400)
we use a modulus calculation in our SQL select statement as follows:
---------------------
SELECT code, descritpion, amount from tbl_samsdata where
(code mod 100)=0 ORDER BY code
---------------------
To select only the rows that are level 2 (eg 210, 220, 310, 320 etc)
we do the same but with a different modulus:
---------------------
SELECT code, description, amount from tbl_samsdata where
(code mod 10)=0 AND (code mod 100)<>0 ORDER BY code
---------------------
That previous statement will get all the rows that are in 10s,
but not the rows which are in 100s (so it will retrieve all level 2
data but not level 1, even though level 1 is divisible by 10)
To get the rest of the rows (ie the actual data, with real money
values) we do a modulus 1 calculation like this:
---------------------
SELECT code, description, amount from tbl_samsdata where
(code mod 1)=0 AND (code mod 10)<>0 AND (code mod 100)<>0 ORDER BY
CODE
---------------------
This last statement is the one which gets the actual useful data.
Really, we can ignore the first two statements except to display
the description of the summary.
I figure I need to do something like this:
SELECT LEVEL 3 and SUM all of the values into a column called "Virtual totals level 3"
Then
SELECT LEVEL 2 and sum all of the column "Virtual totals level 3" AS "Virtual totals level 2"
Then
SELECT LEVEL 1 and sum all of the column "Virtual totals level 2" AS "Virtual totals level 1"
Hmm.. now i'm losing it a bit - erm....
what I have to do is display level 1, and between the level 1 rows (ie between 200 and 300)
I have to display the level 2 rows (ie 210, 220, 230 etc) and between the level 2 rows (ie 210, 220, 230)
I have to display the level 3 rows (ie 211, 221, 222, 231 etc) ... and I have to be able to switch on/off
these rows with a button.
think think think... ponder.. make cup of tea... think think
Urgh....
I just read that it is possible to combine SQL queries (those SELECT statements) and to create
a virtual table from the results, even before this data is sent to the program I am writing (which will display it)
so there is hope there... I am sure this can be done.