order by summed column

G

Guest

Is it possible to order the results of my query by a field that sums the
values of 5 other fields? For example query looks like this:

SELECT projid, tier,
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment)) as
Tot
FROM invfile
WHERE projid IN(projids...,)
AND period=11
AND yr=2005

Result:
projid tier Tot
IY05C92048 1 355
IY05C53028-01 2
IY05C53016-02 2 355
IY05C53016-01 2
IY05C91015 2 355
IY05C53044 3
IY05C91018 3 373
IY05C91017 3 355
IY05C91019 3 355
IY05C91012 3 355
IY05C91010 3 355
IY05C31010 3 355
IY05C31025 3
IY05C53016 Program 355
IY05C53028 Program

I want this to be the order:
projid tier Tot
IY05C91018 3 373
IY05C92048 1 355
IY05C53016-02 2 355
IY05C91015 2 355
IY05C91017 3 355
IY05C91019 3 355
IY05C91012 3 355
IY05C91010 3 355
IY05C31010 3 355
IY05C53016 Program 355
IY05C53028-01 2
IY05C53016-01 2
IY05C53044 3
IY05C31025 3
IY05C53028 Program

Please help,
 
M

Marshall Barton

DB said:
Is it possible to order the results of my query by a field that sums the
values of 5 other fields? For example query looks like this:

SELECT projid, tier,
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment)) as
Tot
FROM invfile
WHERE projid IN(projids...,)
AND period=11
AND yr=2005
Click to expand...

The short way that usually works is:
ORDER BY 3

and the long way is:
ORDER BY
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment))
 
G

Guest

Marshall,

Thank you.

I tried this already and got a type coversion error. So I order by 2 and
noticed the Tot column had an #Error in the column for a record that was
blank. Is there a way to test for nulls and change the Tot field to 0 for
summing?
 
G

Guest

Marshall,

I figured out how to test for nulls and your Order by 3 helped, thank you.
Here's what I did:

SELECT projid, tier,
(iif(isnull(financial),0,ASC(financial))+
iif(isnull(resource),0,ASC(resource))+
iif(isnull(schedule),0,ASC(schedule))+
iif(isnull(technical),0,ASC(technical))+
iif(isnull(alignment),0,ASC(alignment))) AS Tot
FROM invfile
WHERE projid IN(projids...,)
AND period=11
AND yr=2005
ORDER BY 3 DESC , tier;

The result I wanted:
projid tier Tot
IY05C91018 3 373
IY05C92048 1 355
IY05C53016-02 2 355
IY05C91015 2 355
IY05C91017 3 355
IY05C91019 3 355
IY05C91012 3 355
IY05C91010 3 355
IY05C31010 3 355
IY05C53016 Program 355
IY05C53028-01 2 0
IY05C53016-01 2 0
IY05C53044 3 0
IY05C31025 3 0
IY05C53028 Program 0
 
J

John Spencer

Just add the calculation to your order by clause

SELECT projid, tier,
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment))
as
Tot
FROM invfile
WHERE projid IN(projids...,)
AND period=11
AND yr=2005
ORDER BY
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment))

You can use the column number, but if you add any new fields before the
column you are sorting by, the sort will be on a different column name
although on the same column number.

SELECT projid, tier,
(ASC(financial)+ASC(resource)+ASC(schedule)+ASC(technical)+ASC(alignment))
as
Tot
FROM invfile
WHERE projid IN(projids...,)
AND period=11
AND yr=2005
ORDER BY 3
 
Top