Query

[NotSQL]

Sorry to bother you all again.

Im trying to join these two queries together, can you help.

I need all the accounts in query one and a count of meetings that have have taken place which are in query two... I know i need to join on accountName but am unsure how to join the 2 queies

Select AccountName
From dbo.Sales
Group By AccountName

Select AccountName, ISNULL(Count(AccountAccount),0) As NumberOFMeeting
From dbo.V_Time
Group by AccountName

 

[djj55]

It looks like you want all records from sales even if no record is in v_time.

Look at LEFT JOIN also you might want to re-structure your count.

Something like

SELECT S.AccountName, V.NumberOfMeeting
FROM dbo.Sales S 
LEFT JOIN (
     SELECT AccountName, COUNT(*) AS NumberOfMeeting
     FROM dbo.V_Time 
     GROUP BY AccountName
) AS V 
ON S.AccountName = V.AccountName

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[simian336]

Since accountname exist in both queries I am guessing you want to pickup AccountName's that are missing from V_Time?

Simi

 

[gmmastros]

It's hard to see what you are trying to do, because it appears as though you have everything you need from the V_Time table. But... that's because I may not understand the relationships between your tables. One way you could do this is to use a derived table technique, which is basically to embed a query within another one and then treat that query as though it were a table, like this:

Select AccountName, MeetingCount.NumberOfMeeting
From   dbo.Sales
       Inner Join (
         Select   AccountName,
                  ISNULL(Count(AccountAccount),0) As NumberOFMeeting
         From     dbo.V_Time
         Group by AccountName
         ) as MeetingCount
         On Sales.AccountName = MeetingCount.AccountName
Group By Sales.AccountName, MeetingCount.NumberOfMeeting

This code is not tested, so there may be a minor syntax problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NotSQL]

This is what im try to get to, is this the best way of writting this?

Select t1.AccountName, IsNull(t2.NumberOFMeeting,0) as NumberOFMeeting
From
(Select AccountName
From Sales_Matrix
Group By AccountName)t1 left outer join (Select MaginusAccount, ISNULL(Count(AccountName),0) As NumberOFMeeting
From V_TimeCard
Group by AccountName) t2 on t1.AccountName = t2.AccountName