using count on both sides of a join

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

using count on both sides of a join

Author

Topic

wooldawg
Starting Member

12 Posts

Posted - 2008-03-13 : 09:16:24

The query probably speaks for itself. You can see that I had to use a sub select to get the last column. It seems like there must be a better way, but we could not find it. We are on sql 2000. below is the query is the first few rows of the the result set. This query works, being new to sql I am just curious if the sub select could have been avoided in this case. Thanks!

SELECT count(distinct a.store)as [# Stores doing at least 1 Verified Deposit],
b.division,
b.district,
(select count(store_no) from store where district = b.district and fiscal_year = 2008
and current_status = 'a' and owner ='company')as '# of Stores in District'
FROM depositvariance a,
store b
WHERE a.verified_date >'10/25/2007'
and a.store = b.store_no
and b.fiscal_year = 2008
and b.current_status = 'a'
and b.owner ='company'
Group BY b.division,
b.district

# Store that did at least 1 Verified Deposit division district # of Stores in District
-------------------------------------------- -------- -------- -----------------------
42 200 201 44
28 200 202 40
38 200 203 45

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-13 : 09:24:14

try this

SELECT COUNT(DISTINCT a.Store) AS [# Store that did at least 1 Verified Deposit],
b.Division,
b.District,
COUNT(b.Store_No) AS [# of Stores in District]
FROM dbo.DepositVariance AS a
LEFT JOIN Store AS b ON (b.Store_No = a.Store
AND b.Fiscal_Year = 2008
AND b.Current_Status = 'a'
AND b.Owner = 'Company')
WHERE a.Verified_Date > '10/25/2007'
GROUP BY b.Division,
b.District

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 09:25:09

I think this will do the same thing

SELECT		COUNT(DISTINCT CASE WHEN a.Verified_Date > '10/25/2007' THEN a.Store ELSE NULL END) AS [# Store that did at least 1 Verified Deposit],
		b.Division,
		b.District,
		COUNT(DISTINCT a.Store) AS [# of Stores in District]
FROM		dbo.DepositVariance AS a
INNER JOIN	Store AS b ON b.Store_No = a.Store
WHERE		b.Fiscal_Year = 2008
		AND b.Current_Status = 'a'
		AND b.Owner = 'Company'
GROUP BY	b.Division,
		b.District

But it is hard to tell without knowing more about your business rules.

E 12°55'05.25"
N 56°04'39.16"

wooldawg
Starting Member

12 Posts

Posted - 2008-03-13 : 10:05:53

Thanks PeterNeo,

I tried this first too, instead of counting the number of stores under each district in the store table, it takes the number of times each store occurs in the depositvarience table and counts that instead.

# Store that did at least 1 Verified Deposit Division District # of Stores in District
-------------------------------------------- -------- -------- -----------------------
42 200 201 1476
28 200 202 844
38 200 203 1126
18 200 204 490

[quote]Originally posted by PeterNeo

wooldawg
Starting Member

12 Posts

Posted - 2008-03-13 : 10:17:40

Peso,

The depoist varience table contains a row for every time a store does the depoist varience function. It only contains the store number and a verified date.

The store table contains all store numbers (store) along with district and division.
The conditions of

and b.fiscal_year = 2008
and b.current_status = 'a'
and b.owner ='company'

must be used because a store number may appear in the store table in more than one row.

I'm not sure if this helps.

Your query almost worked. I'm confused as to what it is doing though.

district, real # of stores, Peso query
201 44 44
202 40 40
203 45 38
204 28 24
205 27 25
403 51 32
404 52 49
405 47 27
501 37 37
502 50 49
503 48 48
602 56 51
603 39 8
604 43 4
605 25 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 10:28:20

wooldawg -- if you are having trouble with GROUP BY and aggregates when joining several tables, take a look at the following two part article here at sqlteam:

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

It covers what happens when you join a few tables and try to aggregate them, and how to use things like COUNT(DISTINCT) and derived tables to get the results you need.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources