Can't get correct results with JOIN 1

[brettgab]

Here is my problem....

There is a view which displays connection profit amounts for stores.

View CONN_PROFIT

DATE STORE_ID CONN_PROFIT
01-Sep-2001 603 56.00
01-Sep-2001 603 144.00
01-Sep-2001 603 500.00
01-Sep-2001 604 200.00
01-Sep-2001 605 56.00
01-Sep-2001 605 244.00

Then there is another view which holds the total NON_CONN_PROFIT for each store on a given day.

View NON_CONN_PROFIT

DATE STORE_ID NON_CONN_PROFIT
01-Sep-2001 603 300.00
01-Sep-2001 604 100.00
01-Sep-2001 605 500.00

I want to get the TOTAL PROFIT amount, so I'm trying to join these two tables, and this is what I'm getting....

DATE STORE_ID CONN_PROFIT NON_CONN_PROFIT
01-Sep-2001 603 56.00 300.00
01-Sep-2001 603 144.00 300.00
01-Sep-2001 603 500.00 300.00
01-Sep-2001 604 200.00 100.00
01-Sep-2001 605 56.00 500.00
01-Sep-2001 605 244.00 500.00

after joining, and then grouping the results gives me this....

DATE STORE_ID CONN_PROFIT NON_CONN_PROFIT
01-Sep-2001 603 700.00 900.00
01-Sep-2001 604 200.00 100.00
01-Sep-2001 605 300.00 1000.00

What I WANT is this....

DATE STORE_ID CONN_PROFIT NON_CONN_PROFIT
01-Sep-2001 603 700.00 300.00
01-Sep-2001 604 200.00 100.00
01-Sep-2001 605 300.00 500.00

The trouble is that the JOIN occurs first, joining every line in CONN_PROFIT with the NON_CONN profit amount, THEN it sums the results by the grouping I've specified.

What I need it to do is to group CONN_PROFIT first, then join the results with NON_CONN_PROFIT.

HELP !

 

[tlbroadbent]

Try the following query. It utilizes two sub-queries. According to your sample, the second sub-query isn't needed but I wasn't sure if the sample was complete or not. Therefore, the 2nd query summarizes the NON_CONN_PROFIT.

SELECT
a.[DATE],
a.STORE_ID,
a.CONN_PROFIT,
b.NON_CONN_PROFIT
(SELECT
[DATE],
STORE_ID,
Sum(CONN_PROFIT) As CONN_PROFIT
FROM CONN_PROFIT
GROUP BY [DATE], STORE_ID) AS a
INNER JOIN
(SELECT
[DATE],
STORE_ID,
Sum(NON_CONN_PROFIT) As NON_CONN_PROFIT
FROM NON_CONN_PROFIT
GROUP BY [DATE], STORE_ID) AS b
ON a.STORE_ID=b.STORE_ID

NOTE: I recommend that you avoid using reserved words such as DATE as column names. Also, I would avoid using the same name for a table (or view) and a column. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.