ASP, VBscript, SQL Union Select and Record sets [Resolved]

[Matt_T_hat]

I am devloping a new "function" for an ASP website. It is programmed purely in VBscript and uses a Micosoft Access Database.

A loop is used to build an extensive union query. Each loop adds a line such as "...2 as Rel..." the number changes with each iteration dependant on a large array of different factors.

Once we have this huge resord set from the database we will have a list where every entry appears up to Sixty times with only the Rel field being unique to any given line.

What I now want to do is "Sum" on Rel and "Group by" on everything else. I hope there is a way to use SQL on my record set to pull a new record set from it.

I am also hopeing that it does not mean writing a new table in the database each time to do it.

What is the fastest way (In terms of server time) to get to the desired end result? (given that the database is huge and likely to grow endlessly untill we are forced to move to SQL server!)

[Ecniv]

Well, assuming that your union (huge) query is inna string, you need to make it a subquery :

Select field, field, (...etc) ,sum(rel) as sumofrel
From
(
union query here
)

If your union query is run to put the data in a table, which I doubt as you wouldn't have posted up otherwise, then you can do the above but on that table.

Why would you want to sum the `rels` anyway?


Vince

[Matt_T_hat]

Originally posted by Ecniv
Well, assuming that your union (huge) query is inna string, you need to make it a subquery :

Select field, field, (...etc) ,sum(rel) as sumofrel
From
(
union query here
)

You can do that!?! Fantastic! That's easy then. Thank you.

Originally posted by Ecniv
Why would you want to sum the `rels` anyway?

The Sum of Rel should indicate the RELivence of a search term based on how many times it shows up and where.