Is this possible?

Hey guys,

I wrote a T-SQL query to retrieve all errors name and count the error for each error name based on some conditions. Assume that errors_name is a column name in Error table and the error_count is a calculated field. Here is my question; when I run the query, obviously, it displays the list of errors name and the corresponding errors count which only meets the where condition . However, what I need is to list all the errors name (even if they don't meet the condition) and assign a zero value to the error count column for those errors name that do not fulfill the where condition.

Can anybody assist me?

Appreciate your help.

Sincerely,

Amde

given:

create table ErrorSpec(Id, Message)|||

Yeah, just use a left outer join, and then sum on non-null values (hopefully this matches your need. If not, post table structures and data :):

SET NOCOUNT ON
GO
CREATE TABLE errorType
(
errorTypeId varchar(10) PRIMARY KEY
)
CREATE TABLE errorOccurence
(
errorOccurence int IDENTITY PRIMARY KEY,
occurred datetime DEFAULT (getdate()),
errorTypeId varchar(10) REFERENCES errorType(errorTypeId)
)
GO

INSERT INTO errorType
SELECT 'Type1'
UNION ALL
SELECT 'Type2'
UNION ALL
SELECT 'Type3'

GO

INSERT INTO errorOccurence (errorTypeId)
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type3'
go

SELECT errorType.errorTypeId,
sum(case WHEN errorOccurence.errorTypeId IS NOT NULL THEN 1 ELSE 0 end)
FROM errorType
LEFT OUTER JOIN errorOccurence
ON errorType.errorTypeId = errorOccurence.errorTypeId
GROUP BY errorType.errorTypeId

|||

oops. . . yeah I meant left outer join

should have read:

given:

create table ErrorSpec(Id, Message) create table ErrorOccurance(ErrorSpec_Id, DateOfError)|||

Hey guys,

Thank you for your assistance. I already did what you suggested, however adding the left outer join clause doesn't solve my problem.

Here is the code that I wrote:

--

SELECT

e.eventtypename,m.eventtypeid,Count(m.eventtypeid) as [error count],Count(I.instanceid) as [session count]

FROM

eventtypes e

LEFT OUTER JOIN

messages m

ON

e.eventtypeid = m.eventtypeid

LEFT OUTER JOIN

instances i

ON

m.instanceid = i.instanceid

LEFT OUTER JOIN

sessions s

ON

i.instanceid = s.instanceid

LEFT OUTER JOIN

applications a

ON

s.applicationid = a.applicationid

WHERE

m.instanceid IN(SELECT s.instanceid FROM sessions

WHERE(s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513')

AND(a.applicationid = 1002))

AND(I.rootinstanceid = 0)

GROUP BY e.eventtypename,m.eventtypeid

GO

--

The output of this query is as follow:

eventtypename eventtypeid error count session count

Trace 4 492 492

--

So obviously only the data which fulfill the where condition will be displayed. That is true! however, I need also to display those eventtypename which doesn's fulfill the where condition and assign a zero value. For instance, the following table shows the output that I need.

eventtypename eventtypeid error count session count

Trace 4 492 492

Debug failur 2 0 0

Trace warning 3 0 0

Warning event 1 0 0

and so on......

--

I think I am now clear.

Please let me know if you need more explanation?

Sincerely,

Amde

---

|||

remove the timstamp criteria from the where clause and immediately after the "group by" put:

having s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513'

the where is applied at the join point.

the Having statement applies a filter on the grouping

|||

Dear Blair,

I tried it, but it didn't solve my problem.

Let me know if you have another idea.

Sincerely,

Amde

|||post a simple script that makes and populates a similar set of tables.|||

I believe when you create your Left Joins, you should add your filter criteria to the ON clause. Putting your filter criteria in the WHERE will filter out all rows that do not meet the criteria, so when there would be no rows whose COUNT == 0, because the WHERE would filter those rows out. Putting the criteria in the ON clause will at least keep the left portion of the row.

You can do this either by:
1. putting the filter criteria one of the LEFT JOIN clause's. (This looks like it might be more work.)
2. LEFT JOINing the eventtypes table with a subquery that filters the messages table.

It may be easier for you to diagnose if you remove the GROUP BY and COUNTS until you get your rowset looking like:

eventtypename eventtypeid m.eventtypeid I.instanceid
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
<....492 times>
Debug failur 2 NULL NULL
Trace warning 3 NULL NULL
Warning event 1 NULL NULL

then you can add your COUNTs and GROUP BY back and you should have the calculation you are looking for.

Also with representative data we could give more specific assistance.

|||

First. . . you need all your instances where rootinstanceID = 0 with its associated sessions.

then that is used in the left join with messages/events -

try this:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(temp.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN
(
SELECT i.instanceid
FROM instances INNER JOIN sessions s ON i.instanceid = s.instanceid
INNER JOIN applications a ON a.applicationid = s.applicationid
WHERE rootinstanceid = 0
AND s.[timestamp] BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'
) temp
ON m.instanceid = temp.instanceid
GROUP BY e.eventtypename,m.eventtypeid

|||

Thank you guys, both Blair and Todd.

I got what I want. Now it's working

Appreciate it!

Sincerely,

Amde

|||

Dear Blair and Todd,

As I told you earlier, everything works fine, however, I want to bind the error count column with the s.timestamp condition. In other words, I want to count those errors which occured under the given date.

Do you have any idea to implement this functionality with out affecting the previous logic?

Sincerely,

Amde

|||

The other technique to try would be to embed a nested sub-query within one of your columns. This will work better if the relationship between your applications and sessions is not 1-to-n. Without seeing the data, I don't know if this is what you are looking for, but it will allow you to count "error count" and "session count" independently if this is what you need to do. Here is an example syntax with Customers counting Orders:

select
c.CustomerId,
(SELECT count(*) from Orders o where o.CustomerId = c.CustomerId) as o_count
FROM
Customers c

This will work as long as the SELECT statement returns at most 1 row and 1 column.

The sub-select is nested in the column selector, which would allow you to count "applications" and "messages" seperately.

|||

the inline select count is going to be a little slow.

I dont know why my suggestion didnt work. . .

try:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(i.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN instances i on m.instanceid = temp.instanceid
LEFT OUTER JOIN session s ON i.instanceid = s.instanceid
LEFT OUTER JOIN applications a ON a.applicationid = s.applicationid
GROUP BY e.eventtypename,m.eventtypeid
HAVING isNull(rootinstanceid, 0) = 0
AND IsNull(s.[timestamp], '2006-02-25 01:23:52.883')
BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'

Note that 'Having' will not take into account indexes.

Again, I think my nested select should work.

Post some create table/insert statements so I can build a dummy to work with if that doesnt work.

cheers

|||

Dear Blair,

I am not saying your suggestion doesn't work. It is perfect. And your nested select statement works fine. That is really what I wanted. So here is the thing; in the previous code(nested query), the [session count] is filtered based on the s.timestamp value. That is great. Similarly, I need also to filter the [error count] based on the s.timestamp value, so that it will count those errors in the specified timestamp and return a zero value if the given date is not with in the timestamp. In other words, we have to assign a zero value to [error count] if s.timestamp is not between '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513' . Make sure that this modification should not affect the previous functionality.

I appreciate your willingness to assist me.

Amde