this query works,
select distinct a.customer_id, c.customer_name,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is null) as out_standing,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is not null) as paid,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id) as total
from orders a
left join customers c on a.customer_id = C.customer_id
but is it possible to use "(out_standing+paid) as total" to replace the 3rd subselect? fb complains about column unknown:paid
This should work as well (written from the head, haven't tested, as I don't have the DB with these tables/columns):
SELECT a.customer_id, c.customer_name, SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing, SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid, SUM(a.gnd_total) AS total FROM orders a LEFT JOIN customers c ON c.customer_id = a.customer_id GROUP BY a.customer_id, c.customer_name
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...
but in your example the case when field is null then 0 is useful for me.
I am guarding null from data entry but yes there can be null in these sub query.
... but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...You cannot refer to a column alias defined in the same SELECT list. You can only use it in the WHERE and GROUP BY clause. But not in the SELECT list itself.
You cannot refer to a column alias defined in the same SELECT list. You can only use it in the WHERE and GROUP BY clause. But not in the SELECT list itself.
You can do a subselect in FROM with out_standing and paid and then use it in the main SELECT but that's just a workaround.
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...
The answer is no. Not the way you expected.
You can however use subqeries or CTE (https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-cte):
WITH MySubqery AS ( SELECT a.customer_id, c.customer_name, SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing, SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid FROM orders a LEFT JOIN customers c ON c.customer_id = a.customer_id GROUP BY a.customer_id, c.customer_name ) SELECT customer_id, customer_name, out_standing, paid, out_standing + paid AS total FROM MySubquery
You can replace the inner select with the one from your original post, only without the third sum.
oh it looks good. Thanks! I have never been a database guy but I am doing this for a friend so...
oh it looks good. Thanks! I have never been a database guy but I am doing this for a friend so...
I still strongly suggest you to avoid unnecessary subqueries which read the same table as you did in the original query you posted. And, if gnd_total is not declared with "not null" constraint, do use coalesce as I told you in one of the posts above.