join multiple tables with 2 levels of sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join multiple tables with 2 levels of sum

Author

Topic

kazuni
Starting Member

2 Posts

Posted - 2012-03-15 : 13:48:03

[font=Lucida Console]Hi all, I have a question with regards to joining table and display them.

The table schema is as follows:

trades
--------------------------------------------
trade_id trade_name trade_country
--------------------------------------------
T1 tr1 sgp
T2 tr2 phi
T3 tr3 tha
T4 tr4 hkg

trade_article
--------------------------------------------
trade_id trade_article order_id customer
--------------------------------------------
T1 AR1 ORD1 CUST1
T1 AR1 ORD2 CUST2
T1 AR2 ORD1 CUST1
T1 AR3 ORD3 CUST3
T2 AR1 ORD4 CUST4
T2 AR2 ORD5 CUST1
T4 AR3 ORD6 CUST5

trade_article_size
--------------------------------------------
order_id article article_size customer total_qty
--------------------------------------------
ORD1 AR1 SZ1 CUST1 13
ORD1 AR1 SZ2 CUST1 10
ORD1 AR2 SZ1 CUST1 10

ORD2 AR1 SZ1 CUST2 20
ORD2 AR1 SZ2 CUST2 10

ORD6 AR3 SZ1 CUST5 10
ORD6 AR3 SZ2 CUST5 1
ORD6 AR3 SZ3 CUST5 17
ORD6 AR3 SZ4 CUST5 20
...
...

I want to have an aggregated view that loosk like this:

trade_country trade_id trade_name customer total_qty
----------------------------------------------------
sgp T1 tr1 CUST1 33
sgp T1 tr1 CUST2 30
hkg T4 tr4 CUST5 48

How do i go about joining those tables?

(the result table is something like,
a customer in a trade meeting can have multiple article with different sizes, we want their sum of total_qty of all articles in that order of a trade meeting to display.)

if i am not clear, i can perhaps explain a bit more...

Thanks in advance![/font]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:18:10

Seems pretty straight forward...what have you written so far

EDIT: Have you at least written the JOINS?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

kazuni
Starting Member

2 Posts

Posted - 2012-03-15 : 14:25:10

I've got something like...

SELECT distinct trades.trade_id, trades.trade_country, trades.trade_name, trade_article.customer, *(sum(select total_qty from trade_article_size))*

FROM trades

INNER JOIN trade_article
ON trades.trade_id = trade_article.trade_id

INNER JOIN trade_article_size
ON trade_article.order_id = trade_article_size.order_id

something like that... the ones annotated with the asterisks (*) are the part where i am not sure.. I think i am confused in some way.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:32:07

How about


    SELECT t.trade_country, t.trade_id, t.trade_name, ta.customer, SUM(tas.total_qty) total_qty
      FROM trades			t
INNER JOIN trade_article	ta				--With Soul
	ON t.trade_id		= ta.trade_id
INNER JOIN tas				tas
	ON ta.order_id		= tas.order_id
  GROUP BY t.trade_country, t.trade_id, t.trade_name, ta.customer

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources