Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Exclamation Unanswered: Correlated Subquery with a join

    Hello all,

    Can someone help me to make a correlated subquery with a join in it?

    My tables structure as follows

    Table T:
    RptMonth InvNo SaleAmt
    200201 1 100
    200202 2 50
    200202 5 100
    200203 3 100

    Table P:
    InvNo Custtype
    1 A
    2 A
    3 B
    4 C
    5 C

    My select statement should return YTD sales amount for each customer type, something like this:

    RptMonth CustTyp YTDSales
    200201 A 100
    200202 A 150
    200202 C 100
    200203 B 100

    This is the statement I came up with so far:

    SELECT DISTINCT T1.rptmonth, T2.custtype
    (SELECT SUM(sales) FROM T WHERE rptmonth <= t1.rptmonth)
    FROM t t1, p t2 where t1.inv_no = t2.inv_no

    This is not yielding the results that I am looking for. What could i be doing wrong...

    Any help will be greatly appreciated!

    ../Aravind.

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If you want to see all the unique entries try this (untested)

    select RptMonth, CustTyp, sum(sales) as YTDSales
    from
    ...
    ...
    group by RptMonth,CustTyp

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: Correlated Subquery with a join

    SELECT RPTMONTH,
    CUSTTYPE,
    SUM(SALEAMT) AS YTDSALES
    FROM #T1 AS T
    INNER JOIN #T2 TT
    ON T.INVNO = TT.INVNO
    GROUP BY RPTMONTH, CUSTTYPE
    ORDER BY 1
    Shadow to Light

  4. #4
    Join Date
    Aug 2003
    Posts
    3
    Thanks for the replies. However this is not what I am looking for. Looking back at my posting again, I realize that it is little ambiguous.
    Sorry about that.

    Here is a little clarification. Table T has the MTD sales. But the result I would like to see is YTD sales. While both the solutions presented will work for grouping on the RptMonth, it doesnt work for YTD Sales.

    To make it even clearer, if 200201 has 1000 as sales and 200202 as 500 as sales, my query should return 200201, 1000 and 200202, 1500 and so on.

    Regards,

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Try this
    Code:
    set nocount on
    go
    
    create 
    table	#tmpT
    	(
    	RptMonth	int,
    	InvNo		int,
    	SaleAmt		int
    	)
    
    create 
    table	#tmpP
    	(
    	InvNo		int,
    	Custtype	char
    	)
    go
    insert #tmpP (InvNo, Custtype) VALUES (1, 'A')
    insert #tmpP (InvNo, Custtype) VALUES (2, 'A')
    insert #tmpP (InvNo, Custtype) VALUES (3, 'B')
    insert #tmpP (InvNo, Custtype) VALUES (4, 'C')
    insert #tmpP (InvNo, Custtype) VALUES (5, 'C')
    
    insert #tmpT (RptMonth, InvNo, SaleAmt) VALUES (200201, 1, 100)
    insert #tmpT (RptMonth, InvNo, SaleAmt) VALUES (200202, 2, 50)
    insert #tmpT (RptMonth, InvNo, SaleAmt) VALUES (200202, 5, 100)
    insert #tmpT (RptMonth, InvNo, SaleAmt) VALUES (200203, 3, 100)
    
    select	t.RptMonth
    ,	p.Custtype
    ,	(
    	select 	SUM(x.Amt)
    	from 	(
    		select 	SUM(t.SaleAmt) AS Amt,
    			t.RptMonth,
    			p.Custtype
    		from 	#tmpT	t
    		join	#tmpP	p
    		on	t.InvNo = p.InvNo
    		group by
    			p.Custtype,
    			t.RptMonth
    		) x
    	where	x.rptmonth <= t.rptmonth
    	and	x.Custtype = p.Custtype
    	)	as YTD
    
    from	#tmpP	p
    join	#tmpT	t
    on	t.InvNo = p.InvNo
    go
    drop table #tmpP
    drop table #tmpT
    go
    Output
    RptMonth Custtype YTD
    ----------- -------- -----------
    200201 A 100
    200202 A 150
    200203 B 100
    200202 C 100
    MCDBA

  6. #6
    Join Date
    Aug 2003
    Posts
    3
    Works great! It took a little while to make some sense out of it, but eventually it worked.

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •