Pure SQL Query to combine 12 rows and add columns

[mshmyob]

I would like a pure SQL query to do the following:

I have a table that has an unknown set of records created at runtime (this data set changes all the time).

Lets assume I have 100 rows and 3 columns

Column1 is an integer value with data from 1 to 100 (sequentially)

Column 2 is some number that is consistant for all records except the very last record.

Column 3 is a number that is diffrent for each record.

I would like to combine each consequtive 12 rows into a single row and then the three columns should be

Column 1 should then be sequential from 1 to 9 (ie: 100/12=8.3)

Column 2 should be the sum of column 2 of the 12 rows

Column 3 should be the sum of column 3 of the 12 rows

I am currently doing this with a function and saving the results into a temp table.

I would like to know if there is an SQL way of doing this without changing the existing data or creating a new temp table (ie: a select statement is needed)

One more caveat - This database is using SQLite so I need pure SQL and not something specific to Access. I posted here because I did not see an SQLite section.

cheers,

[NeoPa]

AFAIK it can't be done in a single batch, but you could use the TOP 12 predicate to run it in batches so that each batch handled the next set of twelve records. I'm not sure how these separate batches would be controlled outside of VBA though. It's just a basic concept for you to consider.

PS. Oh no! Now you've got me thinking and I think it might be possible after all. I'll have to give it some further thought.

[nico5038]

I would solve this by creating an additional table with two fields:
1) SequenceNumber (1 till max records in the set)
2) "Batch" number being 1 for the first twelve records, 2 for the next, etc.

Now you can join the original table's Column1 with the SequenceNumber and create a query that's using a group by on the "Batch" number.
The first result column will be the Avg() and the other the Sum().

Getting the idea ?

Nic;o)

[NeoPa]

I don't know if the back-slash (\) is generic or Access specific. If the latter then I'm sure there must be some equivalent to provide integer division in SQLLite. I expect Sum(), and the whole of the family of aggregate functions, must be available in all versions of SQL.

Anyway, here's the approach I'd use :

SELECT   (([Col1] - 1) \ 12) + 1 AS [Grouping]
       , Sum([Col2]) AS [Col2Sum]
       , Sum([Col3]) AS [Col3Sum]
FROM     [YourTable]
GROUP BY (([Col1] - 1) \ 12) + 1

[mshmyob]

Thanks Ade that worked perfectly.

I just changed the Access "\" integer division symbol to the standard "/" divison symbol since column1 is an integer the division automatically does integer division (that is the standard for the SQL language).

I also changed "sum" to "total" (a SQLite specific function although sum also works with SQLite but total replaces nulls with a 0).

cheers,

[NeoPa]

Sounds like you have that all down nicely Rob. Good for you :-)

[TheSmileyCoder]

I am often quite gob-smacked by the things that some of you manage to pull of in SQL. I mostly think of SQL as a "Select/Update" kind of thing, and not a language that can be used for computing.

Impressive work, NeoPa.

[NeoPa]

I, too, have been inspired by things I've seen here on Bytes. Before I saw here some of the things that are possible, I used it the same way as most people do, simply for the types of queries that Access can handle for you in Design View.

Seeing some of the stuff that others came up with though (Principally young Rabbit), challenged me to explore it further. That, and doing some work in SQL Server T-SQL where the SQL was all that was available (in the earlier versions, prior to the introduction of the new language interfaces).