Aggregate query based on multiple fields 1

[mmiram]

Hi all:

I have the following table structure:

Address
City
State
Zip
Close Date
Close Price
LID
LA First Name
LA Last Name
SID
SA First Name
SA Last Name
County

The above table reflects the transactions conducted by our agents. Every transaction has two sides, the listing side represented by the LID and the buying side represented by the SID. The field LID is unique to each agent and refers to the Listing Agent and the field SID refers to the buying agent.

Here is what I am trying to get. I am trying to get a result that sums up the total transaction value for all agents. However, here are some rules:

1.) The totals need to be grouped by county and state.
2.) If a house address appears more than once, only the first occurence needs to be taken into consideration.
3.) For every transaction that has two agents i.e. the LID and SID are different for that address, the transaction needs to be counted once for each of those agents.
4.) For every transaction that has the same agent representing both sides of the transaction i.e. the LID and SID are the same, the transaction needs to be counted only once for that agent and not twice.
5.) Lastly, though it is not necessary, it would be nice to have a count. E.g. if LAID 100 had 10 transactions for $2,000,000 then it would be good to know the breakdown for the 10 transactions i.e. 3 listing, 3 buying and 4 double ended transactions.

I hope the query itself is clear. Please let me know if you need any more information. If this cannot be done by one query, it would really help if you can point in the direction I need to proceed.

Thanks for any help.

Ram.

 

[lespaul]

could you provide some sample data and your expected results from that data?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

This is just a general idea rather than finished code

Select Select Address,City,State,Zip,[Close Date],[Close Price],
LID,[LA First Name] & " " & [LA Last Name] As [LName],
SID, [SA First Name] & " " & [SA Last Name] As [SName],
County 
From myTable T
Where [Close Date] = (Select MIN([Close Date]) From myTable X
                      Where X.Address = T.Address)

saved as [blue]qryUAddr[/blue]

Select Agent, Aname, State, County
     , SUM([DoubleEnd])   As [DE]
     , SUM([Buying])      As [Buy]
     , SUM([Selling)      As [Sell]
     , Sum([Total Price]) As [Total]

FROM
(
    Select Q.[LID] As [Agent], Q.[LName] As [AName], Q.[State], Q.[County]
         , 1 As [DoubleEnd], 0 As [Buying], 0 As [Selling]
         , SUM(Q.[Close Price]) As [Total Price]
    From qryUAddr As Q

    Where Q.[LID] = Q.[SID] 

    Group By Q.[LID], Q.[LName], Q.[State], Q.[County], 1, 0, 0

    UNION ALL

    Select Q.[LID] As [Agent], Q.[LName] As [AName], Q.[State], Q.[County]
         , 0 As [DoubleEnd], 1 As [Buying], 0 As [Selling]
         , SUM(Q.[Close Price]) As [Total Price]
    From qryUAddr As Q

    Where Q.[LID] <> Q.[SID] 

    Group By Q.[LID], Q.[LName], Q.[State], Q.[County], 0, 1, 0

    UNION ALL

    Select Q.[SID] As [Agent], Q.[SName] As [AName], Q.[State], Q.[County]
         , 0 As [DoubleEnd], 0 As [Buying], 1 As [Selling]
         , SUM(Q.[Close Price]) As [Total Price]
    From qryUAddr As Q

    Where Q.[LID] <> Q.[SID] 

    Group By Q.[SID], Q.[SName], Q.[State], Q.[County], 0, 0, 1
) As X

Group By Agent, Aname, State, County

 

[mmiram]

Hi Golom:

Thanks for the code. For the most part it works fine with minor syntax changes.

The only change I had to make was to get the right counts.

E.g. I had to change the following code

Select Q.[LID] As [Agent], Q.[LName] As [AName], Q.[State], Q.[County]
, 1 As [DoubleEnd], 0 As [Buying], 0 As [Selling]
, SUM(Q.[Close Price]) As [Total Price]
From qryUAddr As Q


to

Select Q.[LID] As [Agent], Q.[LName] As [AName], Q.[State], Q.[County]
, count(CLPRICE) As [DoubleEnd], 0 As [Buying], 0 As [Selling]
, SUM(Q.[Close Price]) As [Total Price]
From qryUAddr As Q


Where Q.[LID] = Q.[SID]

Group By Q.[LID], Q.[LName], Q.[State], Q.[County], 0, 0


Once I changed it for all the subqueries, it worked great. Thanks a lot. I am going through some of the records in the result set to check for accuracy but it seems to be pretty good.

Thanks,
Ram.