Help with a possible self join

[zeeshanmbutt]

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

I guess I would need a self join with a sub query.
How can I acieve this in SQL?

Please let me know.

Thanks a million.....

 

[ptheriault]

Does this give you what you need?

SELECT Sum(TotalUnits),
Sum(DollarSales),
Sum(Profit),
zonename,
saledate,
catid
FROM TABLE1
GROUP BY zonename,
saledate,
catid

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[zeeshanmbutt]

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.....

 

[AlexCuse]

You can't include saledate if you need to use aggregates, because you will end up grouping by saledate as well. You may consider showing MAX(saledate) instead?

This is a modification of ptheriault's query, and I believe it will return what you're looking for. If not, you will need to be more clear. Are you looking to show each saledate, with the totals across all saledates, or maybe the total up to the row's saledate? Anyway, try this first:

[COLOR=blue]SELECT[/color]  Sum(TotalUnits),
         Sum(DollarSales),
          Sum(Profit),
          zonename,
           [COLOR=#FF00FF]max[/color](saledate), 
            catid
[COLOR=blue]FROM[/color] TABLE1
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color]  zonename,
            catid

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Does SaleDate include time information? If so, then grouping on SaleDate won't work. A slight modification to paul's query might just do the trick for you. Try this...

[COLOR=blue]SELECT[/color] Sum(TotalUnits),
       Sum(DollarSales),
       Sum(Profit),
       zonename,
       [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, saledate), 0) [COLOR=blue]As[/color] SaleDate, 
       catid
[COLOR=blue]FROM[/color] TABLE1
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color]  zonename,
          [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, saledate), 0), 
          catid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom