CODEWITHSUNDEEP
sqlsql-serverjoin
62071072SP
62071072SP

Reputation: 1935

Fetch results count from 2 tables

I am struggling to do the following from morning to get the solution. I have 2 tables

Table1 and Table2

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 the tables and both tables dont have any foreign key relation ships as table1 data is summarised and then updated.

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

When I do some aggregate functions I am not getting results I am expecting.

What I have done so far is 

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 datepart in the aggregate function it is not giving me actual values.

Any help much appreciated!

Upvotes: 4

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

One thought is to use a full outer join to get values even if they are only in one table. I also moved the manufacturer id to the join condition:

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)

Upvotes: 3

Related Questions