How can I use the sum method with a unique attribute

Hello,
I have a query like below, we created custom event (food_journal_entry) and attribute (eventName, state). Throws an event when a request to a 3rd party service fails. We want to calculate the totals amount in the registry in this failed scenario. But we have a problem, In some fail scenarios, there is a retry mechanism and this request retry 3 times so for the same request the amount is *3.
I have a request-based uniq correlationId that tracks my site end-to-end, we also throw this as a custom attribute. But I couldn’t use both uniq and sum function together.

Is there a way to calculate the total amount correctly?

SELECT sum(amount) FROM food_journal_entry WHERE (appName = 'sap-integration-journal-entry-consumer' and eventName = 'foodPaid' and state = 'failed') SINCE 1 month ago

Hi, @buse.sozeyataroglu: Try this:

SELECT sum(amount) 
FROM food_journal_entry 
WHERE appName = 'sap-integration-journal-entry-consumer' 
  AND eventName = 'foodPaid' 
  AND state = 'failed' 
FACET correlationId
SINCE 1 month ago

I tried this but didin’t work . And it also (facet correlationId) shows all correlationId in chart. Like this,

Yes, I thought you want to see sum(amount) for each correlationId?

Nope, I actually want to see total amount for fail state (sum of amounts on all failed requests) .

Hey @buse.sozeyataroglu

You will need a nested query like the following to get the sum of failed requests:

FROM (
FROM food_journal_entry 
SELECT latest(amount) as amount FACET correlationId 
WHERE (appName = 'sap-integration-journal-entry-consumer' and eventName = 'foodPaid' and state = 'failed')
) SELECT sum(amount)

I hope this helps!

2 Likes

It works, Thank you @anil.sonaji !!!

Is there a chance to show more than one query in the same chart ? @anil.sonaji
This is for just failed state . Can we also add for completed, received etc…

FROM (
FROM food_journal_entry
SELECT latest(amount) as amount FACET correlationId
WHERE (appName = ‘sap-integration-journal-entry-consumer’ and eventName = ‘foodPaid’ and state = ‘failed’)
) SELECT sum(amount)

Instead of WHERE state = 'failed' use FACET state:

FROM (
  FROM food_journal_entry 
  SELECT latest(amount) AS amount  
  WHERE (appName = 'sap-integration-journal-entry-consumer' AND eventName = 'foodPaid')
  FACET correlationId, state
) SELECT sum(amount)
FACET state
SINCE 1 month ago