Skip to Content
avatar image
Former Member

How to Sum the same item within the same table by other key?

Hi,

I'm a new beginner for study how to create report on Webi.
Now, I have a difficult to create Sum the same item within the same table by other key.
Maybe difficult to understand. But, I have SQL statement like below.
Can you help me in cretae items: "Success" "Fail" "Avg" "Duration" and "Total"?


Or how to create report (by Universal) look like below SQL?

select
to_char(in.start_time, 'YYYY/MM') Month,
count(distinct user_name) UserName,
count(*) Total,
sum( case when in.status_id ='0' then 1 else 0 end) Success,
sum( case when in.status_id ='1' then 1 else 0 end) Fail,
sum(round((out.start_time - in.start_time) * 24 , 3)) "Duration",
avg(round((out.start_time - in.start_time) * 24 , 3)) "Avg",
from ads_event in,
(select
start_time,
session_id
from ads_event
where event_type_id=9001
and status_id =0) out
where in.event_type_id=9000
and in.session_id=out.session_id
group by to_char(in.start_time, 'YYYY/MM')

Thank you very much.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 17, 2017 at 08:11 PM

    you can create individual objects in the universe for each case statements or you can create variables in webi to calculate these with if-else condition.

    sum( case when in.status_id ='0' then 1 else 0 end) Success,
    sum( case when in.status_id ='1' then 1 else 0 end) Fail,
    sum(round((out.start_time - in.start_time) * 24 , 3)) "Duration",
    avg(round((out.start_time - in.start_time) * 24 , 3)) "Avg",

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 20, 2017 at 07:07 AM

    @Amit: thank for your reply.

    But how i can create two SQL for one object in Universe?

    sum(round((out.start_time - in.start_time) * 24 , 3)) "Duration".

    -> one SQL for "out", and another one SQL for "in", and how alias SQL is "out" or "in" then call it tp create one object?

    Thank you very much.

    Add comment
    10|10000 characters needed characters exceeded