Geek Answers Handbook

Selection of results from 2 tables

I try my best to do the following in the morning to get a solution. I have 2 tables

Table 1 and table 2

Table1

Impressions     Clicks       RetailerId  ManufacturerId      Date
230             1273           5          104             2013-10-23 08:46:21.377
240             1220           12         104             2013-10-23 08:46:21.377
340             1530           8          102             2013-10-23 08:46:21.377
220             2012           25         102             2013-10-23 08:46:21.377

Table2

Earnings        CreatedAt                   RetailerId      ManufacturerId
20.0            2013-10-23 08:46:21.3775       25               104
21.0            2013-10-23 08:46:21.37712      12               104
15.5            2013-10-23 08:46:21.3778       12               102
16.2            2013-10-23 08:46:21.377        25               102

I need to join both tables, and both tables do not have any relationship with a foreign key, since the data in table 1 is summarized and then updated.

My intention is here - I need to get TotalClicks, Total Impressions, TotalEarnings for all retailers for a particular manufacturer and between selected dates.

When I perform some aggregated functions, I do not get the expected results.

What I have done so far

select rs.retailerid, rs.Clicks,asr.TotalValue, rs.ManufacturerId, asr.ManufacturerId
from(select rs.RetailerId,rs.ManufacturerId,SUM(rs.WidgetClicks) as Clicks
from RetailerStats rs group by RetailerId,ManufacturerId)rs
join
(select asr.retailerid,asr.ManufacturerId,SUM(asr.Earnings) as TotalValue
from AffiliateSchemeReports asr group by RetailerId,ManufacturerId)asr
on rs.RetailerId = asr.RetailerId
where rs.ManufacturerId = 104 and asr.ManufacturerId = 104

enter image description here When I give the datepart in an aggregated function, it does not give me the actual values.

Any help is much appreciated!

+4
1

, full outer join , . join:

select coalesce(rs.retailerid, asr.retailerid) as retailerid,
       coalesce(rs.Clicks, 0) as clicks,
       coalesce(asr.TotalValue, 0) as TotalValue,
       rs.ManufacturerId, asr.ManufacturerId
from (select rs.RetailerId, rs.ManufacturerId, SUM(rs.WidgetClicks) as Clicks
      from RetailerStats rs
      group by RetailerId, ManufacturerId
     ) rs full outer join
     (select asr.retailerid, asr.ManufacturerId, SUM(asr.Earnings) as TotalValue
      from AffiliateSchemeReports asr
      group by RetailerId, ManufacturerId
     ) asr
     on rs.RetailerId = asr.RetailerId and
        rs.ManufacturerId = asr.ManufacturerId
where (rs.ManufacturerId = 104 or asr.ManufacturerId = 104)
+3

More articles:


All Articles