Basic select statement with group by... 1

[gjsaturday]

I have the following 3 tables.

TB1
id name date balance
1 TEST 02/09/2005 100

TB2
id orderdate orderamount
1 02/10/2005 10
1 02/12/2005 140

TB3
id saledate saleamount
1 02/10/2005 10
1 02/11/2005 20
1 02/12/2005 30
1 02/13/2005 40

My select is:
select id, sum(balance), sum(orderamount), sum(saleamount)
from tb1, tb2, tb3
where tb1.id = tb2.id
and tb2.id = tb2.id
and tb1.date = CONVERT(VARCHAR,(GETDATE()-1),101)
and tb2.orderdate between CONVERT(VARCHAR,(GETDATE()),101)
and CONVERT(VARCHAR,(GETDATE()+2),101)
and tb2.saledate between CONVERT(VARCHAR,(GETDATE()),101)
and CONVERT(VARCHAR,(GETDATE()+3),101)
group by id

I get:
id balance orderamount saleamount
1 800 600 200

But I want:
id balance orderamount saleamount
1 100 150 100

What am I'm doing wrong.

Thanks in advance!

 

[r937]

you're getting a cross join effect

assuming that all the rows in your examples qualify the various WHERE conditions, the rows produced by the join are as follows:

id balance orderamount  saleamount    
1   100       10            10
1   100      140            10
1   100       10            20
1   100      140            20
1   100       10            30
1   100      140            30    
1   100       10            40
1   100      140            40

then when you do the SUMs, that explains the results you're getting

you can try using SUM(DISTINCT column) but that is not a good strategy and i recommend that you re-think exactly what you want and what you need to join to get it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Whatsoever]

Hey,
I am not even gonna discuss the disatvantages of the query envolved,because I do not see the idea implemented in this query based on using getdate() function, that gives you
current date (besides, I don't know the exact datatype used
for the 'date' column, that could shorten or extend the time range of your scan) - so, please, just describe the idea of your date/time range select criteria and I will try to give you the solution... tommorrow your query will return probably nothing...

 

[EdwinGene]

The problem is the cartesian product created when you join the 3 tables. The cartesian product is as follows:

tb1.id tb1.balance tb2.orderamount tb3.saleamount
1 100 10 10
1 100 10 20
1 100 10 30
1 100 10 40
1 100 140 10
1 100 140 20
1 100 140 30
1 100 140 40

When you invoke the SUM function, it sums all 8 rows in the cartesian product. Using 'distinct' would not help because if two or more rows in any of the amount columns of a table had the same value, the rows with the duplicate amounts would be excluded, and you still would not get the correct totals.

The simplest way I could find to get the results you want is to use the following code snippet:

Begin

Declare @id int
Set @id = 1

Select
@id as id,
(
select sum(balance)
from tb1
where id = @id
) as balance,
(
select sum(orderamount)
from tb2
where id = @id
) as orderamount,
(
select sum(saleamount)
from tb3
where id = @id
) as saleamount

End


I came up with another way which involves (for each column in the select clause) calculating the total number rows in the cartesian product divided by the product of the number of rows in the other tables and then dividing that total into the sum value of the column. But that gets real ugly real fast.

 

[EdwinGene]

gjsaturday,

Just in case you wanted to look at the ugly solution I came up with, here it is:

Select
tb1.id,
Sum(tb1.balance) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb1 where id = 1)) as balance,
Sum(tb2.orderamount) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb2 where id = 1)) as orderamount,
Sum(tb3.saleamount) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb3 where id = 1)) as saleamount
From
tb1 tb1
Inner Join tb2 tb2
on tb1.id = tb2.id
Inner Join tb3 tb3
on tb2.id = tb3.id
Group by
tb1.id

 

[EdwinGene]

Actually, the query in the previous post was more ugly than it needed to be (but not much), and it needs a "where" clause, because in the real world I would assume that there are more values for the id column than just "1".


Select
tb1.id,
Sum(tb1.balance) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb1 where id = 1)) as balance,
Sum(tb2.orderamount) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb2 where id = 1)) as orderamount,
Sum(tb3.saleamount) / (((Select Count(*) from tb1 where id = 1) * (Select Count(*) from tb2 where id = 1) * (Select Count(*) from tb3 where id = 1)) / (Select Count(*) from tb3 where id = 1)) as saleamount
From
tb1
Inner Join tb2
on tb1.id = tb2.id
Inner Join tb3
on tb2.id = tb3.id
Where
tb1.id = 1
Group by
tb1.id

 

[EdwinGene]

gjsaturday,

I have a solution which will list all id's from TB1, along with their balances, order amounts, and sale amounts.

There is a caveat with this solution, however: the values in the "id" column in table TB1 must be unique.


Select
tb1.id,
Sum(tb1.balance) / ((Select Count(*) From tb2 Where id = tb1.id) * (Select Count(*) From tb3 Where id = tb1.id)) as balance,
Sum(tb2.orderamount) / (Select Count(*) From tb3 Where id = tb1.id) as orderamount,
Sum(tb3.saleamount) / (Select Count(*) From tb2 Where id = tb1.id) as saleamount
From
tb1
Inner Join tb2
on tb1.id = tb2.id
Inner Join tb3
on tb2.id = tb3.id
Group by
tb1.id
Order by
tb1.id

 

[gjsaturday]

Thanks edwingene, i gave you a star for this post. And this should work because the id is part of a primary key. To Whatsoever, the reasoning behind the different dates is that I'm going to create a dts package and schedule this to run after our import. I actually need to sum the previous days' balances with the order amounts from today and the next two days (if any), and then subtract our forecasted sales for today and the next three days. Thanks for all your help!