get Count(*) to return 0
-
I have to join two tables that share the column report_Id. I want to return the counts of all report_ids in one table. When there is no match of the report_id in that table in the other table I want to return 0.
How do I do that?
-
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
try with Left join and group by....
-
I want this to return 0 when there is no records found.
Select IsNULL(Count(*), 0) As Count, RT.Report_ID, ReportName, ReportCategory From
tTracking_tblReportsUsage tbTracking
Right Join tblReportsNew RT On tbTracking.Report_ID = RT.Report_ID
Where RT.Report_ID Not In (Select Report_ID From tTracking_tblReportsUsage) And Year(DateStamp) = 2009 Group By RT.Report_ID, ReportName, ReportCategory
-
I tried with left join and group by and it did not work.
-
Check the response from GilaMonster and post the ready to consume DDL and DML scripts....
-
Let me do this time for you...
create TABLE [Department9](
[DepartmentID] [smallint] NOT NULL,
[Name] varchar(1000) NOT NULL
)
create TABLE [EmpDept9](
[DepartmentID] [smallint] NOT NULL,
[empname] varchar(1000) NOT NULL
)
insert into department9 ([DepartmentID],[Name])
select 1,'a' Union all
select 2,'b' Union all
select 3,'c' Union all
select 4,'d'
insert into [EmpDept9] ([DepartmentID],[empName])
select 1,'a1' Union all
select 1,'b1' Union all
select 1,'c1' Union all
select 2,'d1' Union all
select 2,'a1' Union all
select 2,'b1' Union all
select 2,'c1' Union all
select 3,'d1'
select a.departmentid,count(b.departmentid) from department9 a
left join empdept9 b on a.departmentid = b.departmentid
group by a.departmentid
-
Faye Fouladi (12/3/2009)
I want this to return 0 when there is no records found.Select IsNULL(Count(*), 0) As Count, RT.Report_ID, ReportName, ReportCategory From
tTracking_tblReportsUsage tbTracking
Right Join tblReportsNew RT On tbTracking.Report_ID = RT.Report_ID
Where RT.Report_ID Not In (Select Report_ID From tTracking_tblReportsUsage) And Year(DateStamp) = 2009 Group By RT.Report_ID, ReportName, ReportCategory
Try changing
Count(*)
to
Count(tbTracking.Report_ID)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
THIS WILL WORK....DID NOT TEST IT BUT THE METHOD IS SOUND
select a.departmentid
,ISNULL( B.CNT,0)
from department9 a
left join (
SELECT departmentid
, COUNT( * ) CNT
FROM empdept9
GROUP BY departmentid
) b
on a.departmentid = b.departmentid
-
Basically all I had to change was to add 'All' in front of Group BY, this would list all the reports even if the count associated with them was 0.
Group By All ReportName, ReportCategory
Thanks for your ideas.
-
GROUP BY ALL will be removed in a future version of Microsoft SQL Server. Avoid using GROUP BY ALL in new development work, and plan to modify applications that currently use it.
http://msdn.microsoft.com/en-us/library/ms175028(SQL.90).aspx
i never even heard of group by all before.
-
So what is the solution?
-
THIS WILL RETURN THE COUNT OF RECS FROM THE tTracking_tblReportsUsage FOR A GIVEN REPORT_ID AND IF THAT REPORT_ID DOESNT EXIST IN THE tTracking_tblReportsUsage TABLE THEN IT WILL RETURN 0. aLSO IT WONT COUTN AT ALL NOR RETURN ANY REPORT WHO EXISTS IN THE tTracking_tblReportsUsage TABLE. i ASSUME THATS WHAT U WANT CAUSE U HAD A "NOT IN" IN YOUR WHERE WHICH IS ALSO BAD PRECTICE. iT DOESN'T ALWAYS RETURN WHAT YOU WOULD EXPECT.
SORRY FOR THE CAPS. I DIDNT REALIZE I HAD THEM ON.
select RT.Report_ID
, ReportName
, ReportCategory
, IsNULL(TRU.CNT, 0) As [Count]
From tblReportsNew RT
LEFT JOIN (
SELECT Report_ID, COUNT(*) CNT
FROM tTracking_tblReportsUsage
GROUP BY Report_ID
) TRU
On TRU.Report_ID = RT.Report_ID
LEFT JOIN tTracking_tblReportsUsage TRU
On TRU.Report_ID = RT.Report_ID
Where ISNULL(TRU.Report_ID,-1) =-1 --THIS IN CONJUNCTION W/ THE ADDED LEFT JOIN WILL HANDLE YOUR NOT IN
And Year(DateStamp) = 2009
Group By RT.Report_ID, ReportName, ReportCategory
-
Thanks. I will test it.
-
Here is a CTE query I use with SSRS to get the same type information. It works perfectly. Just adjust it for your needs.
;WITH ReportUsageCounts AS
(
SELECT
ReportID
,COUNT(1) AS ReportCount
FROM
dbo.ExecutionLog
GROUP BY
ReportID, Format
)
SELECT
c.ItemID AS ReportID
,c.Name AS ReportName
,ISNULL(u.ReportCount,0) AS ReportCount
FROM
dbo.Catalog c
LEFT OUTER JOIN
ReportUsageCounts u ON c.ItemID = u.ReportID
On a side note: I highly recommend you avoid RIGHT JOIN at all times. They just cause problems which are easy to avoid.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply