sql server programming: Hi,

I have a problem counting the occurence of a number within 3 spefic
columns for one table.

What I want to do is to count the number of occurence of a number (60
for example) for each of the three columns starting by row*. I'm able
to count it seperatly but can't find the correct way to do it for the
three in a single query. Anyone can help me out?

tid gid row1 row2 row3
1 1 20 72 0
2 1 60 0 0
3 1 77 73 0
4 1 60 0 0
5 1 77 60 74
6 1 74 60 21
7 1 77 60 73
8 2 83 9 63
9 2 1 26 0
10 3 10 0 0


SELECT COUNT(*) as col1result FROM theTable
WHERE row1=60

ANS: 2

SELECT COUNT(*) as col2result FROM theTable
WHERE row2=60

ANS: 3

SELECT COUNT(*) as col3result FROM theTable
WHERE row3=60

ANS: 0

I'd like to have

col1result col2result col3result
----------------------------------------
2 3 0

In a single query.

Thanks in advance.

Thanks Vishal.

It works fine!

Now I have another problem. I'd like to count the occurence of all the
distinct numbers present in the three columns (except 0 which is in fact
a dummy) and show the results in that fashion:

number row1 row2 row3
----------------------
1 1 0 0
9 0 1 0
10 1 0 0
20 1 0 0
21 0 0 1
26 0 1 0
60 2 3 0
63 0 0 1
72 0 1 0
73 2 3 0
74 1 3 1
77 3 0 0
83 1 0 0

Is it possible to do it in a single query ? How can I have my query to
get the distinct value from 3 columns at a time?

I tried this but get way to much combinations (I get 3 three columns
instead of only one with 1-9-10-20-21-26-60-63-72-73-74-77-83)

SELECT DISTINCT row1, row2, row3 FROM theTable

And How can I iterate threw the distinct numbers (if I get them right)?

I'm using SQL Server 2000.

Again thx for precious help.

John


*** Sent via Developersdex http://www.developersdex.com ***

Try:

SELECT sum(case row1 when 60 then 1 else 0 end ) as col1result ,
sum(case row2 when 60 then 1 else 0 end ) as col2result ,
sum(case row3 when 60 then 1 else 0 end ) as col3result
FROM theTable

--
-Vishal
[quoted text, click to view]

If you just want the answer, this is ugly because I do not know how to join
the subqueries, although I have seen it done.

/* The technique is to create a union of the individual row queries
then compute the sum of the values */
select row1 as value, count(*) as good from dataTable
where row1 in
(select distinct row1 from dataTable
where row1 <> 0)
group by row1
union
select row2 as value, count(*) as good from dataTable
where row2 in
(select distinct row2 from dataTable
where row2 <> 0)

group by row2

union
select row3 as value, count(*) as good from dataTable
where row3 in
(select distinct row3 from dataTable
where row3 <> 0)
group by row3
order by value compute sum(count(*)) by value


It gives you the sum, at least you can check your work.

[quoted text, click to view]

I had to use an alias for the subquery in order for it to work

select ... from ( subquery as ti) group by value

I also used union all

select distinct value, sum(good) from
( select row1 as value, count(*) as good from dataTable
where row1 in
(select distinct row1 from dataTable
where row1 <> 0)
group by row1
union all
select row2 as value, count(*) as good from dataTable
where row2 in
(select distinct row2 from dataTable
where row2 <> 0)

group by row2

union all
select row3 as value, count(*) as good from dataTable
where row3 in
(select distinct row3 from dataTable
where row3 <> 0)
group by row3
) as sa
group by value


[quoted text, click to view]

Sorry for the delay,
Is this what you are looking for?

create table #t(number int, row1 int, row2 int, row3 int)
insert into #t values( 1 ,1 ,0 ,0)
insert into #t values( 9 ,0 ,1 ,0)
insert into #t values( 10 ,1 ,0 ,0)
insert into #t values( 20 , 1 , 0 , 0)
insert into #t values( 21 ,0 , 0 , 1)
insert into #t values( 26 , 0 , 1 , 0)
insert into #t values( 60 ,2 , 3 , 0)
insert into #t values( 63 ,0 , 0 , 1)
insert into #t values( 72 ,0 , 1 , 0)
insert into #t values( 73 ,2 , 3 , 0)
insert into #t values( 74 ,1 , 3 , 1)
insert into #t values( 77 ,3 , 0 , 0)
insert into #t values( 83 ,1 , 0 , 0)

--Query
select distinct number , row from
(select number,row1 row from #t where row1 <> 0
union all
select number,row2 from #t where row2 <> 0
union all
select number,row3 from #t where row3 <> 0) a

--
-Vishal
[quoted text, click to view]

Hi everyone.

I broke my right hand and was in a cast since july 13!

About my count problem: let me be a little bit more clear.

What i'm trying to do is to count the statistics of hockey players
(goals, assists and game played)

Table team_info : contains team_id (PK) and other info about a team

Table user_info : contains user_id (PK) and other info about a user

Table game : contains the game_id (PK) and visiting_team_id,
home_team_id

Table player_team_game : contains game_id, user_id, team_id

Table goal : contains goal_id (PK), game_id, user_id (goal), pla_user_id
(assist #1), pla_user_id2 (assist #2)

What I want to do is to count the goals and assists as well as game
played and have the results shown in such a manner:

user_id games_played goals assists
----------------------------------------
80 4 5 2
78 4 4 0
1 4 3 1
5 3 2 2
24 4 0 0


I just can't figure out how to assign the value 0 to a player that plays
the games but does not score a goal or an assist. When using count,
those users don't come out.

create table team_info (team_id, team_name)
insert into team_info values( 1,'PAX' )
insert into team_info values( 2,'WIL' )

create table user_info (user_id, user_name)
insert into user_info values( 0,'dummy' )
insert into user_info values( 1,'john' )
insert into user_info values( 5,'frank' )
insert into user_info values( 24,'chris' )
insert into user_info values( 78,'daivd' )
insert into user_info values( 80,'vishal' )

create table game (game_id, visiting_team_id, local_team_id)
insert into user_info values( 1,1,2 )
insert into user_info values( 2,1,2 )
insert into user_info values( 3,1,2 )
insert into user_info values( 4,1,2 )

create table player_team_game (game_id, user_id, team_id)
insert into player_team_game values( 1,1,1 )
insert into player_team_game values( 1,24,2 )
insert into player_team_game values( 1,78,2 )
insert into player_team_game values( 1,80,1 )
insert into player_team_game values( 2,1,1 )
insert into player_team_game values( 2,5,1 )
insert into player_team_game values( 2,24,2 )
insert into player_team_game values( 2,78,2 )
insert into player_team_game values( 2,80,1 )
insert into player_team_game values( 3,1,1 )
insert into player_team_game values( 3,5,1 )
insert into player_team_game values( 3,24,2 )
insert into player_team_game values( 3,78,2 )
insert into player_team_game values( 3,80,1 )
insert into player_team_game values( 4,1,1 )
insert into player_team_game values( 4,5,1 )
insert into player_team_game values( 4,24,2 )
insert into player_team_game values( 4,78,2 )
insert into player_team_game values( 4,80,1 )

create table goal (goal_id, game_id, user_id, pla_user_id, pla_user_id2)
insert into goal values( 1,1,80,0,0 )
insert into goal values( 2,1,80,1,5 )
insert into goal values( 3,1,78,0,0 )
insert into goal values( 4,2,1,80,0 )
insert into goal values( 5,2,80,0,0 )
insert into goal values( 6,2,1,80,0 )
insert into goal values( 7,2,78,0,0 )
insert into goal values( 8,3,80,0,0 )
insert into goal values( 9,3,78,0,0 )
insert into goal values( 10,3,78,0,0 )
insert into goal values( 11,3,1,0,0 )
insert into goal values( 12,4,80,5,0 )
insert into goal values( 13,4,5,0,0 )
insert into goal values( 14,4,5,0,0 )

All values are int except for team_name and user_name which are
VARCHAR16.

the user_id 0 stands for a dummy value to be inserted in the goal table
for un assisted goals (because pla_user_id and pla_user_id2 are foreign
keys)

Can anyone help me out?


--------
John

*** Sent via Developersdex http://www.developersdex.com ***