Aggregate, group by question

[bhandarip]

Customer Sales1 Sales2
1 $100.00 $200.00
2 $50.00 $300.00
3 $40.00 $400.00
4 $900.00 $500.00
5 $600.00 $600.00
4 $300.00 $500.00

SELECT test.Customer, Sum(test.Sales1) AS SumOfSales1, Sum(test.Sales2) AS SumOfSales2
FROM test
WHERE (test.Sales1) > (test.Sales2)
GROUP BY test.Customer;

I was expecting to get the following result:
Customer Sales1 Sales2
4 $1,200 $1,000

However, I am getting this result:
Customer Sales1 Sales2
4 $900 $500

What am I doing wrong?

Thank you very much!!!

 

[bhandarip]

nevermind....I figured it out.

 

[bhandarip]

Re: another question....

Why can't I join these two tables on Customer(Not Primary key) field and get the SUM from Test1 table correctly? My Sale1 and Sale2 amount from Test2 double for customer A an D.

My query:
SELECT DISTINCT test2.Customer, Sum(test2.Sales1) AS SumOfSales1, Sum(test2.Sales2) AS SumOfSales2
FROM test2 INNER JOIN test ON test2.Customer = test.Customer
GROUP BY test2.Customer;

Results:

Customer SumOfSales1 SumOfSales2
A $1,800 $400
B $50 $300
C $40 $400
D $2,400 $2,000
E $600 $600

Test1
Customer Sales1 Sales2
A $100 $200
B $50 $300
C $40 $400
D $900 $500
E $600 $600
D $300 $500
A $800 $0

Test2
Customer Sales1 Sales2
A $100 $200
B $50 $300
C $40 $400
D $900 $500
E $600 $600
D $300 $500
A $800 $0
X $5,000 $10,000


What am I doing wrong? Is this what's called Cartesian product?

Thanks!!!

 

[Norie]

Aren't the sums correct?

Test1 Customer A Sum of sales1 = 900
Test2 Customer A Sum of sales1 = 900

and the sum of that = 1800.

 

[bhandarip]

No, they are not. I did NOT explain it well. I wanted the sum from Test2 table ONLY even though I have to join these two tables to get common customers to both tables.

The correct sum should have been:

Customer Sales1 Sales2
A $900 $200
D $1,200 $1,000

Am I making any sense?

Thank you!

 

[Norie]

So is the sales info in the two tables is the same?

ie. it's repeated in both

Why do you have that?

 

[bhandarip]

No, the Sales amount could be different. I just happened to put the same numbers. I am NOT joining in Sales fileds though.