Help with a self join !!! - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a self join !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-26 : 15:02:25

Hi All,

I have a table called Table1 with the following fields and types.

ZoneName(nvarchar),OutletID(nvarchar),CatID(nvarchar),SaleDate(datetime),TotalUnits(decimal),DollarSales(decimal),Profit(decimal).

Using the above Table1 I want to get the Sum(TotalUnits),Sum(DollarSales),Sum(Profit) per zonename, per saledate, and per catid, where;
its same ZoneName, same CatID, and same OutletID

That is in my select Query i want the following fields:

ZoneName,CatID,SaleDate,Sum(TotalUnits),Sum(DollarSales),Sum(Profit)

And the join should be on the following fields:
ZoneName
CatID
OutletID

Based on above criteria how can I create a self join (with a possible sub query) ?

How can I acieve this in TSQL?

Please let me know.

Thanks a million.....

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 15:28:04

You don't want a join, you want grouping, something like this

SELECT ZoneName, CatID, dateadd(dd, 0, datediff(dd, 0, getdate())) AS SaleDate
    , Sum(TotalUnits) AS SumTotalUnits, Sum(DollarSales) AS SumDollarSales, Sum(Profit) AS SumProfit
FROM Table1
GROUP BY ZoneName, CatID, dateadd(dd, 0, datediff(dd, 0, getdate())

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-26 : 15:29:54

No, it doesn't give what I am looking for.

I want to create a self join on Table1 to return the following fields:
ZoneName,CatID,SaleDate,Sum(TotalUnits),Sum(DollarSales),Sum(Profit)

Where its same: (that is the join should be on the following):

ZoneName
CatID
OutletID

How can I achieve this in TSQL?

Someone please let me know this as soon as possible.

Thanks a million.....

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 15:44:58

quote:
Originally posted by zeeshan13

No, it doesn't give what I am looking for.

I want to create a self join on Table1 to return the following fields:
ZoneName,CatID,SaleDate,Sum(TotalUnits),Sum(DollarSales),Sum(Profit)

Where its same: (that is the join should be on the following):

ZoneName
CatID
OutletID

How can I achieve this in TSQL?

Someone please let me know this as soon as possible.

Thanks a million.....

Doesn't make sense...read the hint link in my sig and post what itt asks for

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources