order by calculated value

[tragik]

i'm running 2 count(*) subqueries and want to order by the difference between
the 2.

select
column_a,
(select count(*) from blah where x = y) as test_1,
(select count(*) from blah where x = z) as test_2
where
a = b
order by
[the difference between test_1 and test_2] desc


please help! :) if you need clarification let me know.

[Dan Bracuk]

You have to order by a field in one of the tables in your select clause. You
can't order by a number, unless that number represents the 1st, 2nd, etc field
in your select clause.

In your example, if test1 turned out to be 50, and test2 was 5, your query
would effectively be:

select column_a, 50, 5
from mytable
order by 45

On the bright side, if your db supports sub-queries in the select clause, you
have the right idea. Your query needs a from clause of course.

[tragik]

sorting by a static variable won't produce any actual sorting; sorting by a
calculated field that changes based on the relational value will produce a
difference calculation for each row, will work fine.

maybe you just misunderstood what i was after..and yah i left out the from by
mistake ;)

select
z.column_a,
(select count(*) from blah where x = z.y) as test_1,
(select count(*) from blah where x = z.z) as test_2
from
table_c z
where
z.a = '#b#'
order by
[the difference between test_1 and test_2] desc

hope that clears up any confusion!

[Dan Bracuk]

Try this:
select
z.column_a,
(select count(*) from blah where x = z.y) as test_1,
(select count(*) from blah where x = z.z) as test_2, test_1 - test_2 as diff
from
table_c z
where
z.a = '#b#'
order by diff

[Dan Bracuk]

Upon further review, having the subqueries in the select clause makes no sense
because they return the same number in every row. You have to put them in the
where clause, and include a field on which to join.

select field1, thecount
from table1 join
(select field1, count(field1) as the count
from
sometables
group by field1) x using (field1)
etc

The syntax is db specific.

[paross1]

Just create a 4th column in your SELECT where you determine the difference
between column 2 and 3, then use positional notation in the ORDER BY, so that
you are sorting on column 4, which is actually a calculation.

SELECT column_a,
(SELECT COUNT(*) FROM blah WHERE x = y) as test_1,
(SELECT COUNT(*) FROM blah WHERE x = z) as test_2,
(SELECT COUNT(*) FROM blah WHERE x = y)-(SELECT COUNT(*) FROM blah WHERE x =
z) as diff
FROM your_table
WHERE
a = b
ORDER BY 4 DESC

Phil

[tragik]

i was thinking along the lines of setting the subqueries as variables to avoid another subquery, but that works just fine.

thanks! :)

[paross1]

Test_1 and test_2 are column aliases, not "variables". Most databases that I
know of usually do not allow you to use the column aliases in the ORDER BY or
GROUP BY clauses, so you usually need to repeat the same expression as used in
the select statement, or in the case of ORDER BY, you can use the relative
"column" position number.

Phil