SQL: Calculating a complex column?

Question

- I have a query which returns a list of names.

- But along with that list of names I need a second column of true/false values.

- These true/false values are calculated by taking the difference of two SELECT SUM(x)... queries for each name returned.

Can I do this with a single SQL statement? I don't think I can, but if I can I will.

If I need a stored procedure, can someone point me in the right direction, how should I best perform the original query then add a column for each row based on a newly computed value? I have limited experience with more simple stored procedures than this one.

Example:
david true
rob false
john false

where true/false is calculated by two scalar queries (psudo): (select sum(avail) from table) - (select sum(used) from othertable)

Answer 1

there are a couple of things you have to do.

its where clause, subqueries, alias.



i will explain with an example.



first of all you didn't say anything about the condition which will define true or false.



in mysql/sql 0 and 1 is considered as boolean. so that's what you will get returned not true and fasle.



then there is Scalar subqueries. which we will need for the 2nd column.



now it's time to write the code.



see this example,



select a from ( select 2 - 4 as a) as b;



you will get a value -2.



which means we create a column of scalar values from some manipukation and use that in Outer Sql query.



You know the querry to get the difference. you wrote in. Say it's

now just to let you know, you can have either 1 or 0 , not true or false.



so the querry will be,



select names, values from ( X as values).



you may run into some problem. but this format will work. otherwise try to find the errors if there is any.

Answer 2

That depends on what database engine you are using. Most databases have tables that contain the database structure and often have database specific commands to show them. For instance, in MySQL you can use "show tables" to get a list of all the table names in the database, then recursively run through the tables using the "describe tables" command to get all the column names to compare to the one you are seeking.

Answer 3

You need to code the following:

SELECT name, (select sum(avail) from table) - (select sum(used) from othertable) as condition

FROM sometable

WHERE ...

etc.



You can reference sometable fields from the inner selects as long as there is a different reference name as in

SELECT name, (select sum(avail) from table b) - (select sum(used) from othertable c) as condition

FROM sometable a

WHERE ...

etc.



Have fun.

Answer 4

The where operator would work for you if are trying to separate the false from the true.



SELECT column_name(s)

FROM table_name

WHERE column_name operator value