after joining two tables the result does not match with the original table

Question

I have problem with inner joining 2 tables: LOOKUP and PERF.

LOOKUP

...has only creative name and "perf." table has the creative name as well as the values to it. I need to get the values into LOOKUP (where both the table has common creative name). FYI: UCID is nothing but creative name

Also, creative names have duplicates in both the tables, so i need to sum them up and get the distinct value. Below is the code what i have used but sum() function seems not to be working as i am seeing the different value when compared against the original table data.

Here is the code i have used

select UCID/*, [creative name]*/,
       sum(perf.[delivered impressions]) as ttl_del,        
       sum(perf.[IMM_SUMMARY])as ttl_WSO,
       sum(perf.[clicks]) as ttl_clicks,
       sum(perf.[IMM_SUMMARY])/sum(perf.[delivered impressions])*100 as IMM_Score    
  from ADHOC_NG_UCID_lookup lookup    
  JOIN FILTERED_CREATIVE_EXEC_TABLE perf ON lookup.[UCID] = perf.[creative name]    
 where perf.[delivered impressions] > 0 
   and perf.[brand] in ('MALIBU') 
   and perf.month in ('APRIL') 
   and perf.[sizes] in('160x600')    
group by UCID/*, lookup.[creative name]*/    
order by UCID/*, [creative name]*/

Here is the code where i where use to cross check for the values (this is the original table)

select [creative name],
        sum([Delivered Impressions]) asttl_del,
        sum([IMM_SUMMARY])asttl_WSO,
        sum([clicks])as ttl_clicks,
        sum([IMM_SUMMARY])/sum([Delivered Impressions])*100 as IMM_Score
from FILTERED_CREATIVE_EXEC_TABLE
where [delivered impressions] > 0 
  and [brand] in('MALIBU')
  and month in('APRIL')
  and [sizes] in('160x600')
group by[creative name]
order by[creative name]

Please advice or let me know why the sum() function is not working properly.

Greatly appreciate your support.

Answer 1

Are there rows in FILTERED_CREATIVE_EXEC_TABLE that do not exist in ADHOC_NG_UCID_lookup? If so, the INNER JOIN would exclude those rows from your first query. To check for this condition:

select perf.[creative name]
    from FILTERED_CREATIVE_EXEC_TABLE perf
        LEFT JOIN ADHOC_NG_UCID_lookup lookup
            ON perf.[creative name] = lookup.[UCID]
    where perf.[delivered impressions]>0 
        and perf.[brand] in ('MALIBU') 
        and perf.month in ('APRIL') 
        and perf.[sizes] in('160x600')
        and lookup.[UCID] is null

Answer 2

you probably have duplicates in your data.

run these. if either one returns data that means those records are duplicates

select
    UCID, COUNT(*)
from ADHOC_NG_UCID_lookup lookup
group by UCID
having COUNT(*)>1


select
    [creative name], COUNT(*)
from FILTERED_CREATIVE_EXEC_TABLE perf
group by [creative name]
having COUNT(*)>1