How do I get count to issue a 0??

Hello all and thank you for your time.
Table 1:
Johnny BallTypeId= 1
Debbie BallTypeId= 2
Kurt BallTypeId=1
Table 2:
1 Baseballs
2 Footballs
3 Golf Balls
Desired Output:
Baseballs 2
Footballs 1
Golf Balls 0
NULL 23
SQL:
select t2.BallTypeDesc, Count(*)
from table1 t1 left outer join table2 t2 on t1.BallTypeId =
t2.BallTypeId
group by t2.BallTypeDesc
This results in the following:
Baseballs 2
Footballs 1
NULL 23
So how do I get a row for Golf Balls totaling 0?
Thanks for you help,
Sunshinetry using group by all...
select t2.BallTypeDesc, Count(*)
from table1 t1 left outer join table2 t2 on t1.BallTypeId =
t2.BallTypeId
group by ALL t2.BallTypeDesc
<sunshinevaldes@.yahoo.com> wrote in message
news:1146662250.719115.100970@.y43g2000cwc.googlegroups.com...
> Hello all and thank you for your time.
> Table 1:
> Johnny BallTypeId= 1
> Debbie BallTypeId= 2
> Kurt BallTypeId=1
> Table 2:
> 1 Baseballs
> 2 Footballs
> 3 Golf Balls
> Desired Output:
> Baseballs 2
> Footballs 1
> Golf Balls 0
> NULL 23
> SQL:
> select t2.BallTypeDesc, Count(*)
> from table1 t1 left outer join table2 t2 on t1.BallTypeId =
> t2.BallTypeId
> group by t2.BallTypeDesc
> This results in the following:
> Baseballs 2
> Footballs 1
> NULL 23
> So how do I get a row for Golf Balls totaling 0?
> Thanks for you help,
> Sunshine
>|||On Wed, 3 May 2006 09:27:40 -0400, "Jim Underwood"
<james.underwoodATfallonclinic.com> wrote:

>try using group by all...
>select t2.BallTypeDesc, Count(*)
>from table1 t1 left outer join table2 t2 on t1.BallTypeId =
>t2.BallTypeId
>group by ALL t2.BallTypeDesc
Jim, you got me with that one, I've never seen GROUP BY ALL before.
And here I thought I knew SQL! 8-)
Roy Harvey
Beacon Falls, Ct|||That works great! I have never come across that one. I really
appreciate your help!
Sunshine|||I learned it this monday, or maybe last w, on this newsgroup. It was the
first time I had seen it myself.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:e3eh521r4db5s0n53v7kfonji6skejggt1@.
4ax.com...
> On Wed, 3 May 2006 09:27:40 -0400, "Jim Underwood"
> <james.underwoodATfallonclinic.com> wrote:
>
> Jim, you got me with that one, I've never seen GROUP BY ALL before.
> And here I thought I knew SQL! 8-)
> Roy Harvey
> Beacon Falls, Ct|||I believe this works.
CREATE TABLE Table1
(name varchar(20) not null,
BallTypeId tinyint not null)
CREATE TABLE Table2
(BallTypeId tinyint not null,
BallTypeDesc varchar(20) not null)
GO
INSERT Table1 VALUES('Johnny', 1)
INSERT Table1 VALUES('Debbie', 2)
INSERT Table1 VALUES('Kurt' , 1)
INSERT Table1 VALUES('Ralph', 0)
INSERT Table2 VALUES(1,'Baseballs')
INSERT Table2 VALUES(2,'Footballs')
INSERT Table2 VALUES(3,'Golf Balls')
select t2.BallTypeDesc, Count(T1.name)
from table2 t2
full outer
join table1 t1
on t1.BallTypeId = t2.BallTypeId
group by t2.BallTypeDesc
I tried the GROUP BY ALL that Jim mentioned, but it didn't seem to
make any difference. I think it is because GROUP BY ALL is a sort of
workaround for the effect of the WHERE clause, and there is no WHERE
clause. But FULL OUTER join seems to have done the job.
Roy Harvey
Beacon Falls, CT
On 3 May 2006 06:17:30 -0700, sunshinevaldes@.yahoo.com wrote:

>Hello all and thank you for your time.
>Table 1:
>Johnny BallTypeId= 1
>Debbie BallTypeId= 2
>Kurt BallTypeId=1
>Table 2:
>1 Baseballs
>2 Footballs
>3 Golf Balls
>Desired Output:
>Baseballs 2
>Footballs 1
>Golf Balls 0
>NULL 23
>SQL:
>select t2.BallTypeDesc, Count(*)
>from table1 t1 left outer join table2 t2 on t1.BallTypeId =
>t2.BallTypeId
>group by t2.BallTypeDesc
>This results in the following:
>Baseballs 2
>Footballs 1
>NULL 23
>So how do I get a row for Golf Balls totaling 0?
>Thanks for you help,
>Sunshine|||> Jim, you got me with that one, I've never seen GROUP BY ALL before.
> And here I thought I knew SQL! 8-)
GROUP BY ALL is an old Sybase left-over. I used to teach it in my SQL classe
s, but stopped a number
of years ago because you rarely see it used (or have need for it). Also, I t
ry to avoid it because
it clouds the mental/logical sequence for how a SELECT statement is performe
d. In a non-beginner
class, perhaps.. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:e3eh521r4db5s0n53v7kfonji6skejggt1@.
4ax.com...
> On Wed, 3 May 2006 09:27:40 -0400, "Jim Underwood"
> <james.underwoodATfallonclinic.com> wrote:
>
> Jim, you got me with that one, I've never seen GROUP BY ALL before.
> And here I thought I knew SQL! 8-)
> Roy Harvey
> Beacon Falls, Ct