Need to display ALL rows, even with Zero count

Question

Post reply

Need to display ALL rows, even with Zero count

jsmoritz

Grasshopper

Points: 19
More actions
April 17, 2009 at 5:09 pm

#199589

I have 2 tables. tblTime has a list of times, every fiteen minutes for 8 hours. The other table has records associated with the time the referral was given. When I use the following SQL statement and generate the result in a graph, it does not include any times with no referrals and I need to graph ALL of the times.
Notice there are several times missing in the result? I need all the times from tblTime with a value of 0 included in the result. Does anyone know how I can do this?
SELECT TOP (100) PERCENT
strTime, COUNT(*)
FROM tblTime INNER JOIN
tblReferral ON timeID = intRefTime
GROUP BY strTime
StrTime COUNT
8:15 AM 1
8:45 AM 1
9:00 AM 1
9:15 AM 6
9:30 AM 1
9:45 AM 5
10:00 AM 9
10:15 AM 3
10:30 AM 3
10:45 AM 1
11:00 AM 2
11:15 AM 5
11:30 AM 2
11:45 AM 11
12:00 PM 5
12:15 PM 6
12:30 PM 3
12:45 PM 3
1:00 PM 1
1:15 PM 5
1:30 PM 11
1:45 PM 4
2:00 PM 7
2:15 PM 6
2:30 PM 8
2:45 PM 3
3:00 PM 10
3:15 PM 4
3:30 PM 3
3:45 PM 3
4:00 PM 9
4:15 PM 6
4:30 PM 4
4:45 PM 3
5:00 PM 1

Viewing 8 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

April 17, 2009 at 5:12 pm

#980066

Change your inner join to a left outer join.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 2

As Lynn pointed out - change your join to an outer join. Also, get rid of the TOP 100 PERCENT - it is doing nothing at all for you and is not needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jsmoritz Grasshopper Points: 19 More actions · Answer 3

I tried that but I get the same records..:unsure: Any other ideas?

jsmoritz Grasshopper Points: 19 More actions · Answer 4

SQL Management Studio keeps throwing in the TOP (100) PERCENT, even when I delete it, it immediately comes back. I did change it to a left outer join and still get the same records. Thanks for the help, any other ideas?

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

I see - you are using the query builder, which does stupid things like that. What you should do is just open a new query window and write the code yourself.

Can you show us what you ended up with when you changed the join?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Paul White SSC Guru Points: 150468 More actions · Answer 6

I know it's deprecated, but GROUP BY ALL would work here:

CREATE TABLE #t1 (a INT IDENTITY(1,1) PRIMARY KEY)

CREATE TABLE #t2 (a INT IDENTITY(5,1) PRIMARY KEY)

SET NOCOUNT ON

GO

INSERT #t1 DEFAULT VALUES;

INSERT #t2 DEFAULT VALUES;

GO 10

SELECT#t1.a, COUNT_BIG(*)

FROM#t1, #t2

WHERE#t1.a = #t2.a

GROUPBY

ALL #t1.a

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

The problem is probably what you're grouping by. Which tables do all the columns belong to? The easiest way to describe that is to apply correct table aliases to your original code below...

SELECT strTime, COUNT(*)

FROM tblTime INNER JOIN

tblReferral ON timeID = intRefTime

GROUP BY strTime

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)