Archived

This topic is now archived and is closed to further replies.

Crispy

Need to optimize an SQL query

Recommended Posts

The situation is as follows: I have a table that contains all entrties for a given Type each of which has a YearOfInstallation value and a couple of properties I need to use to classify them by, like this:
Length [other properties] YearOfInstallation
...    ...                ...
1300   ...                1982
815    ...                1991
1900   ...                1993
1600   ...                2002
...    ...                ...
I need to construct a query in MS Access that creates a number of groups from the data in the table:
Group                SumOfLength
...                  ...
1980-1990            1300
1991-2000            2715
2001-2003            1600
...                  ...
The easiest way to do this is by creating a separate query for each of the groups, but it seems awfully inefficient. I can't figure out if it's impossible to create such a query (since adding several where-conditions into the SQL statement in different places is illegal) or I have to use some advanced techniques. Access wizard fails hopelessly in this case. Additionally - it is not possible to create automated parametrized queries for reports (which is the case). I'd appreciate some help (running on a deadline - god I hate clients that make up stuff on the go...). Crispy EDIT: formatting [edited by - crispy on March 18, 2003 8:11:12 AM]

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
it''s been a while for me with sql, but some rdms''s i use to work on allowed a GROUP BY on a calculated column. try adding a column to your SELECT that calculates the Group from the YearOfInstallation by dividing it by 10, and then using a GROUP BY for that column, e.g.,

select YearOfInstallation/10, sum(Length)
from ...
where ...
group by YearOfInstallation/10,
order by YearOfInstallation/10

Share this post


Link to post
Share on other sites
What that does is that it groups the attributes by Year/10, e g, the result is somenthing like:

199,9 1300
198,4 12342

etc.

Besides, the groups aren''t always 10 years in size. I''m actually given periods, such as 0-5, 6-10, 10-20 etc years old. In fact I believe I stated my question wrong in the original post . Damn.
Thanks for the reply, though - any other ideas?

Crispy

Share this post


Link to post
Share on other sites
Here is an easy way to do that.

Add another field to have the group inside of it.

Then use an update query.

update tblData set GroupID = GroupID from
tblData cross join tblGroups where
YearOfInstallation between group.startyear and group.endyear
and GroupID is null

You may have to use table names in-front of the fields because of ambiguous names.

Share this post


Link to post
Share on other sites
afterburn: this won''t do for a very simple reason: the groups are dynamic - the objects move from one group to the next once they became a certain number of years of age. What you''re suggesting would require updating the group numbers every year - that''s something I can''t rely on.

However, I decided it would be easier to run a bunch of Dsum''s in VBA instead and fill in the 25 or so fields one by one instead.

Thanks for the replies, though!

Crispy

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
use UNIONS

{
select *
WHERE time between "bla bla" and "bla bla + 10"
}
UNION
{
select *
WHERE time between "bla bla + 10" and "bla bla + 20"
}
UNION
{
select *
WHERE time between "bla bla + 20" and "bla bla + 30"
}
....
....

etc.
of course use group by too to group those selects, this is just a simple example which will form a result table all different groups are unified in one table

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
ok ok i have to edit my previous post here it is

(
SELECT ''Group 1'' AS GroupID, SUM(Length) AS Total
FROM xTable
WHERE YearOfInstallation BETWEEN 1980 AND 1985
)
UNION
(
SELECT ''Group 2'' AS GroupID, SUM(Length) AS Total
FROM xTable
WHERE YearOfInstallation BETWEEN 1986 AND 1990
)
UNION
(
SELECT ''Group 3'' AS GroupID, SUM(Length) AS Total
FROM xTable
WHERE YearOfInstallation BETWEEN 1991 AND 1995
)

this will give a result set of

GroupID Total
.......... ..........
Group 1 x
Group 2 y
Group 3 z

x,y,z being the sums (sorry about x,y,z am currently working on 3d stuff hehehh ) )

Share this post


Link to post
Share on other sites
Teehee - I remember doing something like this once - Access choked on it real bad. It''s (Access) rather useless when datasets and queries become complex and/or large. I''m not sure, but I''m relatively confident it isn''t the underlying SQL database that poses a 65k character query length limit (although you can fit a world in that) - it has a lot of other limitations and errors to produce...

Share this post


Link to post
Share on other sites