By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Pure SQL Query to combine 12 rows and add columns

mshmyob
Expert 100+
P: 903
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,
Feb 5 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   (([Col1] - 1) \ 12) + 1 AS [Grouping]
  2.        , Sum([Col2]) AS [Col2Sum]
  3.        , Sum([Col3]) AS [Col3Sum]
  4. FROM     [YourTable]
  5. GROUP BY (([Col1] - 1) \ 12) + 1

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 5 '12 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 5 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   (([Col1] - 1) \ 12) + 1 AS [Grouping]
  2.        , Sum([Col2]) AS [Col2Sum]
  3.        , Sum([Col3]) AS [Col3Sum]
  4. FROM     [YourTable]
  5. GROUP BY (([Col1] - 1) \ 12) + 1
Feb 5 '12 #4

mshmyob
Expert 100+
P: 903
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,
Feb 6 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
Sounds like you have that all down nicely Rob. Good for you :-)
Feb 6 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Feb 6 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
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).
Feb 6 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.