Outer Join with Aggregate problem

Question

Post reply

Outer Join with Aggregate problem

qutip

SSC Eights!

Points: 870
More actions
April 3, 2012 at 7:16 am

#255877

Hi All,
I've been banging my head against a brick wall trying to figure out why?!
I have a table of Stores, a table of Transactions, transaction details and a Table of Regions:
SELECT Dimensions.Stores.Store
, Dimensions.Stores.StoreCode
, Dimensions.Regions.Region
, COUNT(Transactions.TransactionID) AS CountOfTransactions
FROM Transactions INNER JOIN
TransactionDetails ON Transactions.TransactionID = TransactionDetails.TransactionID RIGHT OUTER JOIN
Dimensions.Stores INNER JOIN
Dimensions.Regions ON Dimensions.Regions.LocationID = Dimensions.Locations.LocationID INNER JOIN
Dimensions.Regions ON Dimensions.Location.RegionID = Dimensions.Regions.RegionID ON
Transactions.StoreID = Dimensions.Stores.StoreID
WHERE (Dimensions.Regions.Region = 'North West') AND (Transactions.TranDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-12-31 00:00:00', 102))
GROUP BY Dimensions.Stores.Store, Dimensions.Regions.Region, Dimensions.Stores.StoreCode
I get a correct count of all transactions at each store within the region, even the store with zero transactions but when I drop the Between Date in I only get the stores with transactions and I also want the zero transactions too.
Any ideas?
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

in order to returns zeros from your COUNT on transactions, you will have to use a CROSS JOIN i think.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Qutip (4/3/2012)
Hi All,
I've been banging my head against a brick wall trying to figure out why?!
I have a table of Stores, a table of Transactions, transaction details and a Table of Regions:
SELECT Dimensions.Stores.Store
, Dimensions.Stores.StoreCode
, Dimensions.Regions.Region
, COUNT(Transactions.TransactionID) AS CountOfTransactions
FROM Transactions INNER JOIN
TransactionDetails ON Transactions.TransactionID = TransactionDetails.TransactionID RIGHT OUTER JOIN
Dimensions.Stores INNER JOIN
Dimensions.Regions ON Dimensions.Regions.LocationID = Dimensions.Locations.LocationID INNER JOIN
Dimensions.Regions ON Dimensions.Location.RegionID = Dimensions.Regions.RegionID ON
Transactions.StoreID = Dimensions.Stores.StoreID
WHERE (Dimensions.Regions.Region = 'North West') AND (Transactions.TranDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-12-31 00:00:00', 102))
GROUP BY Dimensions.Stores.Store, Dimensions.Regions.Region, Dimensions.Stores.StoreCode
I get a correct count of all transactions at each store within the region, even the store with zero transactions but when I drop the Between Date in I only get the stores with transactions and I also want the zero transactions too.
Any ideas?
TIA

Hello and welcome to SSC!

I'd like to be able to help, but it seems you've forgot to post readily consumable sample data and ddl scripts.

If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.

Thanks!

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

qutip SSC Eights! Points: 870 More actions · Answer 3

I think I've found the answer:

SELECT Dimensions.Stores.Store

, Dimensions.Stores.StoreCode

, Dimensions.Regions.Region

, COUNT(Transactions.TransactionID) AS CountOfTransactions

FROM Transactions INNER JOIN

TransactionDetails ON Transactions.TransactionID = TransactionDetails.TransactionID AND TranDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 102) RIGHT OUTER JOIN

Dimensions.Stores INNER JOIN

Dimensions.Regions ON Dimensions.Regions.LocationID = Dimensions.Locations.LocationID INNER JOIN

Dimensions.Regions ON Dimensions.Location.RegionID = Dimensions.Regions.RegionID ON

Transactions.StoreID = Dimensions.Stores.StoreID

WHERE (Dimensions.Regions.Region = 'North West') AND (Transactions.TranDate BETWEEN CONVERT(DATETIME, '2011-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-12-31 00:00:00', 102))

GROUP BY Dimensions.Stores.Store, Dimensions.Regions.Region, Dimensions.Stores.StoreCode

I'm just cross checking my results now. Fingers crossed.