sql, inner join did not return right value

Hi Experts,

If I run
1) query A
select  sum([Alternatedealmtd]) as AlternatedealMTD
      ,sum([ManualDealmtd]) as ManualDealMTD
            ,sum([wtd]) as WTD
      ,sum([mtd]) As MTD
      ,sum([lastmonth]) As LastMonth
,sum(lastmonthtotal) as LastMonthTotal
      ,case when sum(mtd)>0 then (cast(sum(MTD) as float)-cast(Sum(lastmonth)as float))/sum(lastmonth) *100
        else 0 end as Trending,
        count(distinct t.dealid)as testdrive
     FROM [MMD_Feed].[dbo].[DART] AS dt
      inner  JOIN Mmd_feed..deal d on dt.dealerid=d.dealerid
      inner JOIN [MakeMyDeal_com].[dbo].[TestDrive] t on t.dealid=d.dealid
  where cast(d.submittedtimestamp as date)>=DateAdd("d",-(Day(getdate())-1), getdate()) and cast(d.submittedtimestamp as date)<getdate()

It returned me
AlternateDealMTD 165
ManualDealMTD 31
WTD  31
MTD 2105
LastMonth 1679
LastMonthTotal 2659
Treding 25
TestDrive 99

If I run
2) query B
SELECT  sum([Alternatedealmtd]) as AlternatedealMTD
      ,sum([ManualDealmtd]) as ManualDealMTD
            ,sum([wtd]) as WTD
      ,sum([mtd]) As MTD
      ,sum([lastmonth]) As LastMonth
,sum(lastmonthtotal) as LastMonthTotal
      ,case when sum(mtd)>0 then (cast(sum(MTD) as float)-cast(Sum(lastmonth)as float))/sum(lastmonth) *100
        else 0 end as Trending
  FROM [MMD_Feed].[dbo].[DART]

It returned me
AlternateDealMTD 87
ManualDealMTD 28
WTD  92
MTD 2532
LastMonth 1795
LastMonthTotal 2832
Treding 41

My question is query B has the correct result. But, I have add testdrive in the return value, but query A did not return me a correct result. I tried use left join, right join in query A and the result looks too ridiculous.

Please find attached sample data. Can u help on it? Thanks.

Sample.xlsx
tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
You do not have the same WHERE clause in query B. In fact, you don't have a WHERE clause at all in query B.
0
tanj1035Author Commented:
I have to where clause in query A in order to get value of test drive. Since I have added the where clause in query A, the value of AlternateDealMTD,ManualDealMTD  etc... became different from query B. Query B has the correct value for AlternateDealMTD,ManualDealMTD  etc...
0
dsackerContract ERP Admin/ConsultantCommented:
Do you get the same results without the WHERE clause? If so, it may become necessary to SUM your totals in a subquery.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tanj1035Author Commented:
If I add the where clause, the result is different. This is the problem. So, I do not know how to fix it. Can you show me the query? Thanks.
0
dsackerContract ERP Admin/ConsultantCommented:
Keep in mind, I'm shooting in the dark, since I don't know your data.

It seems like your testdrive count and your sum should really be two separate queries. To join them both, I've put them in two separate subqueries, as follows

SELECT  *
FROM   (SELECT sum([Alternatedealmtd]) as AlternatedealMTD
              ,sum([ManualDealmtd]) as ManualDealMTD
              ,sum([wtd]) as WTD
              ,sum([mtd]) As MTD
              ,sum([lastmonth]) As LastMonth
              ,sum(lastmonthtotal) as LastMonthTotal
              ,case
                when sum(mtd)>0 then (cast(sum(MTD) as float)-cast(Sum(lastmonth)as float))/sum(lastmonth) * 100 
                else 0
               end as Trending
        FROM  [MMD_Feed].[dbo].[DART]) t1,
       (SELECT count(distinct t.dealid) as testdrive
        FROM [MMD_Feed].[dbo].[DART] AS dt
        inner JOIN Mmd_feed..deal d on dt.dealerid=d.dealerid 
        inner JOIN [MakeMyDeal_com].[dbo].[TestDrive] t on t.dealid=d.dealid 
        WHERE cast(d.submittedtimestamp as date)>=DateAdd("d",-(Day(getdate())-1), getdate())
        and   cast(d.submittedtimestamp as date)<getdate()) t2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanj1035Author Commented:
Thanks, it works! This is the new thing for me to learn!!!
0
dsackerContract ERP Admin/ConsultantCommented:
Glad that worked.

Be careful with separate queries in a sub-select. I intentionally did not join them, therefore they are called "cartesian" queries. If you leave off the WHERE clause that joins two queries like this, which returns detail records rather than SUMs, you can wind up with a runaway query.

Think of one entire table being fetched for every row in another entire table without condition. That's a cartesian query. It's worth reading about.

This was one of those rare times where it's justified.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.