Subquery not working
Guys, do you know how to create a subquery to bring extra data on the SQL? I've look on the docs, but it doesnt seem to work (I only get null values).
This is the subQuery:
And in the main query:
Thank you in advance =]
13 Replies
Hello, @Noronha! Could you please tell what you want to achieve with this query?
Hi @solo how are you? 🙂
I have two tables here:
Contract and Transaction.
Transaction holds financial info like, expiredAt, paidAt, value [...] and maybe a contractId.
So When listing contracts, I need to get all the Transaction values that were paid for that contract. That's why I'm trying an subquery.
@Noronha I am fine, thanks! Correct me if I am wrong, but I don't think that you need a subquery here. If you just want to get all contracts with the
totalValue
of transactions that are associated with each contract, you can do something like this:
In this code, I cast totalValue
to integer
just as an example (if you need to cast you should cast to value
data type), because sum()
returns a string. Also, I didn't include where
clauses.Good morning @solo , hope this message finds you well 🙂
Sorry for taking this long to give you some feedback
I think you're right and we're getting close to a solution.
The problem is the sum value looks like an random number. Some examples:
totalPaid: 1.387.332
should be: 231.222
totalPaid: 1035
should be: 345
totalPaid: 3123
This one was correct.
Dont understand these results.
I got it working like this:
But if there's a better way to do without the subquery I think its better, can you help me achive that?
Hello, @Noronha! Just to clarify, are you aiming to sum up all transaction values for each contract with filters, pagination, etc. However, there seems to be an issue with the totalPaid value not being accurate. Additionally, you've provided three different results - is this due to changes in your filter criteria, or is there another reason?
You got it almost 100% right. The different values that I showed you was:
- totalPaid coming from the Query
- The actual value that it should print.
This means that the SUM is not being executed on the correct values, for some reason.
Ok, thanks
totalPaid: 3123
This one was correct.
did you change something to get correct value?
Nope Just one was correct.
@Noronha sorry for many questions, do this code works for you as expected?
Yes it does and I already shipped in to production. But
As I said without subquery would be better I think
dont worry man, I'm just so grateful for your attention and time, you're the best.
super, could you tell me please for what is used
coalesce
in subquery?I use it because its a left join, so I might have null "sum"s, and in those cases I want the result to be 0
not "left join", I mean maybe a contract doesnt have any payments yet
I believe using a subquery might be a good approach for this case as well. Considering other options might require a reproduction repository, but I'm not certain it's necessary for you to create one. However, I might be able to assist with the query to make it more readable:
contract: contracts,
- retrieve all columns from contracts table