473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Pure SQL Query to combine 12 rows and add columns

mshmyob
904 Expert 512MB
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

7 3853
NeoPa
32,556 Expert Mod 16PB
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
3,080 Expert 2GB
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
32,556 Expert Mod 16PB
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
904 Expert 512MB
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
32,556 Expert Mod 16PB
Sounds like you have that all down nicely Rob. Good for you :-)
Feb 6 '12 #6
TheSmileyCoder
2,322 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

4
by: Stuart E. Wugalter | last post by:
Here is a sample of what I want to do: Table 1 ID FIELD1 FIELD2 1 A T 2 G C 3 T C Table2 ID FIELD1 FIELD2
10
by: DBLWizard | last post by:
Howdy, I need to compine two columns (LastName, Firstname) with the comma. The only problem I have is the Firstname could be blank and in that case I don't want the "," appended to the last...
1
by: dayhill | last post by:
I am trying to combine three columns (month, day, year) to make one date (month-day-year). Here is the intital inforation when I hit the SQL button in Microsoft Query ...
2
denny1824
by: denny1824 | last post by:
Hi everyone, Here is my problem. I have a Table Variable (I could easily turn it into a Temp Table instead) that will sometimes have rows that are identical except for one specific column. The...
1
by: bcr123 | last post by:
Hello. Could you please help with following problem in Microsoft Excel: I have thousands or rows of 5min data that I need to convert to 15min and later to 20min data do some calculations. ...
2
by: mzmatterafact | last post by:
I'm back and please be warned I'm a total NEWBIE, and i've had success with my previous post so I would like to buy another vowel! Now i have taken my csv file and imported to a DataTable, i've...
2
by: kkshansid | last post by:
same query return rows in mysql but not on php page while($rs = mysql_fetch_array($sql2)) { $town=$rs; $q="select * from institute where address like '%".$town."%'";//this query //echo $town;...
6
by: viki1967 | last post by:
Hi all. I need your help. I realize this script and I do not know where to begin: 1) A simple grid rows / columns. 2) The first column contain an input type = "checkbox" 3) When select...
3
by: ronakinuk | last post by:
how can i unhide rows/columns in excel 2007
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.