SQL - Using Count Function - 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

General SQL Server Forums

New to SQL Server Programming

SQL - Using Count Function

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-19 : 05:51:50

Hi,

I am currently writing a query which is showing the number of rows related to items on another table. But i also want the query to return 0 if the item has 0 rows related to it - is that possible?

Here is the code:

SELECT dbo.TBL_SUB_POLICY.SUB_POLICY_NAME, COUNT(dbo.TBL_PC_PROFILE.PC_NAME) AS Count, dbo.TBL_WORKSTATION.SUB_POLICY_ID
FROM dbo.TBL_PC_PROFILE INNER JOIN
dbo.TBL_WORKSTATION ON dbo.TBL_PC_PROFILE.PC_PROFILE_ID = dbo.TBL_WORKSTATION.PC_PROFILE_ID INNER JOIN
dbo.TBL_SUB_POLICY ON dbo.TBL_WORKSTATION.SUB_POLICY_ID = dbo.TBL_SUB_POLICY.SUB_POLICY_ID
GROUP BY dbo.TBL_SUB_POLICY.SUB_POLICY_NAME, dbo.TBL_WORKSTATION.SUB_POLICY_ID

niall

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 06:06:24

Use a LEFT OUTER JOIN instead of an INNER JOIN?

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-19 : 06:24:14

unfortunately it didnt work...it added another row in the query which said i had 6 null values, which is the number of items i want to display with a 0 value...

niall

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 06:30:05

You have columns from both TBL_WORKSTATION and TBL_SUB_POLICY in your SELECT, and you have INNER JOINS to both tables, so you cannot find records that do not have a join to either table.

You can add an OUTER JOIN to one/both those tables, but then the columns in the SELECT referencing them will be NULL.

You can use OUTER JOIN and remove the corresponding column from the SELECT (and the GROUP BY) and add a column COUNT(dbo.TBL_WORKSTATION.SUB_POLICY_ID) or COUNT(dbo.TBL_WORKSTATION.PC_PROFILE_ID) to get the Count of rows in that JOIN'd table

Note that the COUNT will give a warning message because it is being asked to include NULL values; that may cause trouble in your Client Application - if it is not expecting/handling any Warning messages.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-19 : 06:34:32

You mean like this?

SELECT		sp.SUB_POLICY_NAME,
		COUNT(pp.PC_NAME) AS Count,
		w.SUB_POLICY_ID
FROM		dbo.TBL_WORKSTATION AS w
INNER JOIN	dbo.TBL_SUB_POLICY AS sp ON sp.SUB_POLICY_ID = w.SUB_POLICY_ID
LEFT JOIN	dbo.TBL_PC_PROFILE AS pp ON pp.PC_PROFILE_ID = w.PC_PROFILE_ID
GROUP BY	sp.SUB_POLICY_NAME,
		w.SUB_POLICY_ID

N 56°04'39.26"
E 12°55'05.63"

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-19 : 08:37:24

would mind showing me an example? im not sure what you mean....

thanks!

niall

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 09:47:39

Try the one Peso provided

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources