Oracle SQL - Count only one of specified value

Question

I do hope someone can help me with this.

I am doing two count functions on on of my columns, both specified by where the column has a specific text (see below code). On my count for meeting (WHERE EVENT_TYPE = 'meeting_start'), I have found that this can pop up more than once within the same meeting. I need to only count this once per meeting event id and cannot find a way to do this.

select I.CRM_INSTANCE_NAME,
       C.CUSTOMER_NAME,
       U.USER_NAME,
       ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0') AS USER_ACTIVATION_DATE,
       ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0') AS LAST_ACTIVITY_DATE,
       COUNT(E.EVENT_TYPE) AS MEETING,
       COUNT(F.EVENT_TYPE) AS FINALISED

       FROM SBX_USERS U
        LEFT JOIN SBX_CUSTOMERS C on U.CUSTOMER_ID = C.CUSTOMER_ID
        LEFT JOIN SBX_CRM_INSTANCES I on C.CRM_INSTANCE_ID = I.CRM_INSTANCE_ID
        LEFT JOIN SBX_MEETING M on U.USER_ID = M.USER_ID
        LEFT JOIN SBX_EVENTS E ON SOURCE_ID  = M.MEETING_ID
        LEFT JOIN (SELECT * FROM SBX_EVENTS WHERE EVENT_TYPE = 'meeting_start' AND EVENT_CREATION_DATE >=add_months(sysdate, -12)) E on E.SOURCE_ID = M.MEETING_ID
        LEFT JOIN (SELECT * FROM SBX_EVENTS WHERE EVENT_TYPE = 'finalize'AND EVENT_CREATION_DATE >=add_months(sysdate, -12)) F on F.SOURCE_ID = M.MEETING_ID

where U.ENABLED_FLAG IN ('Y','E')

group by  I.CRM_INSTANCE_NAME, C.CUSTOMER_NAME, U.USER_NAME, ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0'), ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0')
order by  I.CRM_INSTANCE_NAME;

Above is the code I am using at that moment, thanks in advance for your help.

Regards

Dr

This involves a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregations over appropriate rows and/or aggregate a case statement picking rows; join on a unique column set. Sometimes DISTINCT aggregation picks the right values after a non-key join. (A join on a non-key of either of 2 input tables can give multiple rows for each key of each table.) — philipxy, Jun 01 '20 at 14:38
Duplicate of [sum data from multiple tables](https://stackoverflow.com/q/2591390/3404097) — philipxy, Jun 01 '20 at 14:39

score 0 · Answer 1 · 2020-06-01T14:26:54.993

You need a group by inside your subselect, what you call 'Table E', like so:

select I.CRM_INSTANCE_NAME,
       C.CUSTOMER_NAME,
       U.USER_NAME,
       ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0') AS USER_ACTIVATION_DATE,
       ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0') AS LAST_ACTIVITY_DATE,
       COUNT(E.MEETING_NO) AS MEETING,
       COUNT(F.EVENT_TYPE) AS FINALISED

       FROM SBX_USERS U
        LEFT JOIN SBX_CUSTOMERS C on U.CUSTOMER_ID = C.CUSTOMER_ID
        LEFT JOIN SBX_CRM_INSTANCES I on C.CRM_INSTANCE_ID = I.CRM_INSTANCE_ID
        LEFT JOIN SBX_MEETING M on U.USER_ID = M.USER_ID
        LEFT JOIN SBX_EVENTS E ON SOURCE_ID  = M.MEETING_ID
        LEFT JOIN (SELECT EVENT_TYPE,SOURCE_ID,count(*) MEETING_NO FROM SBX_EVENTS WHERE EVENT_TYPE = 'meeting_start' AND EVENT_CREATION_DATE >=add_months(sysdate, -12) GROUP BY EVENT_TYPE,SOURCE_ID) E on E.SOURCE_ID = M.MEETING_ID
        LEFT JOIN (SELECT * FROM SBX_EVENTS WHERE EVENT_TYPE = 'finalize'AND EVENT_CREATION_DATE >=add_months(sysdate, -12)) F on F.SOURCE_ID = M.MEETING_ID

where U.ENABLED_FLAG IN ('Y','E')

group by  I.CRM_INSTANCE_NAME, C.CUSTOMER_NAME, U.USER_NAME, ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0'), ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0')
order by  I.CRM_INSTANCE_NAME;

You need a group by inside your subselect, what you call 'Table E'. I have posted the solution above. I hope this one helps to correct your query. — , Jun 01 '20 at 14:22
you should put all the relevant information about your solution in the answer itself. I have edited your answer to include the relevant information from your comment - you should now delete your comment. — Boneist, Jun 01 '20 at 14:27

score 0 · Answer 2 · answered Jun 01 '20 at 14:23

You don't need the two extra outer joins from sbx_events, you can use conditional aggregation instead, e.g. something like:

select I.CRM_INSTANCE_NAME,
       C.CUSTOMER_NAME,
       U.USER_NAME,
       ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0') AS USER_ACTIVATION_DATE,
       ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0') AS LAST_ACTIVITY_DATE,
       COUNT(DISTINCT CASE WHEN E.EVENT_TYPE = 'meeting_start' AND eVENT_CREATION_DATE >=add_months(sysdate, -12) THEN 1 END) AS MEETING,
       COUNT(CASE WHEN E.EVENT_TYPE = 'finalize' AND eVENT_CREATION_DATE >=add_months(sysdate, -12) THEN 1 END) AS FINALISED
       FROM SBX_USERS U
        LEFT JOIN SBX_CUSTOMERS C on U.CUSTOMER_ID = C.CUSTOMER_ID
        LEFT JOIN SBX_CRM_INSTANCES I on C.CRM_INSTANCE_ID = I.CRM_INSTANCE_ID
        LEFT JOIN SBX_MEETING M on U.USER_ID = M.USER_ID
        LEFT JOIN SBX_EVENTS E ON SOURCE_ID  = M.MEETING_ID
where U.ENABLED_FLAG IN ('Y','E')
group by  I.CRM_INSTANCE_NAME, C.CUSTOMER_NAME, U.USER_NAME, ltrim(TO_CHAR(U.CREATED_DATE ,'mm-yyyy'),'0'), ltrim(TO_CHAR(U.LAST_UPDATED_DATE ,'dd-mm-yyyy'),'0')
order by  I.CRM_INSTANCE_NAME;

N.B. you had two tables aliased as e, so I've taken a guess as to which one you meant in the counts.

Also, if you only care about the meeting_start and finalize event_types, you could add that as a join condition from the sbx_events table, along with the date restriction.

score 0 · Answer 3 · answered Jun 01 '20 at 14:27

In essence, I think that you want COUNT(DISTINCT ...).

But also, your query is not valid SQL: you have two aliases called E: the logic that joins sbx_events is problematic. I suspect that you actually want one join on that table (with a join condition on the date), and conditional aggregation in the COUNT(DISTINCT).

That would be:

select 
    i.crm_instance_name,
    c.customer_name,
    u.user_name,
    ltrim(to_char(u.created_date ,'mm-yyyy'),'0') as user_activation_date,
    ltrim(to_char(u.last_updated_date ,'dd-mm-yyyy'),'0') as last_activity_date,
    count(distinct case when event_type = 'meeting_start'  then m.meeting_id end) as meeting,
    count(distinct case when event_type = 'finalize'  then m.meeting_id end) as finalized
from sbx_users u
left join sbx_customers c 
    on u.customer_id = c.customer_id
left join sbx_crm_instances i 
    on c.crm_instance_id = i.crm_instance_id
left join sbx_meeting m 
    on u.user_id = m.user_id
left join sbx_events e 
    on source_id  = m.meeting_id
    and  e.event_creation_date >= add_months(sysdate, -12))
where u.enabled_flag in ('y','e')
group by  
    i.crm_instance_name, 
    c.customer_name, 
    u.user_name, 
    ltrim(to_char(u.created_date ,'mm-yyyy'),'0'), 
    ltrim(to_char(u.last_updated_date ,'dd-mm-yyyy'),'0')
order by  i.crm_instance_name;

Oracle SQL - Count only one of specified value

3 Answers3