Professional Web Applications Themes

Count problem - Microsoft SQL / MS SQL Server

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 ...

  1. #1

    Default Re: Count problem

    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
    John Grenier <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

  2. #2

    Default Re: Count problem

    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 ***
    Don't just participate in USENET...get rewarded for it!
    John Guest

  3. #3

    Default Re: Count problem

    >Player 5 did not play in game 1 but has an assist in game 1.

    Got a 5 minute penalty for fighting too and was selected 2nd star of the game. :-)
    raydan Guest

  4. #4

    Default Re: Count problem

    Hey, I think I know that guy! ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "raydan" <nospamcom> wrote in message news:e$phx.gbl... 

    Got a 5 minute penalty for fighting too and was selected 2nd star of the game. :-)

    Tom Guest

  5. #5

    Default Re: Count problem

    > Hey, I think I know that guy! ;-)

    Of course you do, he plays for Toronto. :-(
    raydan Guest

  6. #6

    Default Re: Count problem

    Oh, here we go. I take it you're a Canadiens fan? ;-) La troiseme etoile ... the turd star ... :-O

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "raydan" <nospamcom> wrote in message news:uq7a##phx.gbl... 

    Of course you do, he plays for Toronto. :-(

    Tom Guest

  7. #7

    Default Re: Count problem

    > Oh, here we go. I take it you're a Canadiens fan? ;-) La troiseme etoile ... the turd star ... :-O

    That's "la troisième étoile",
    .... and "shame on me", I can't even name one Canadiens player.

    Let's put an end to this thread.
    There are 3 things that should not be discussed in this newsgroup.
    Sports, religion and ANSI standards.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Oh, here we go. I take it you're a Canadiens fan? ;-) La troiseme etoile ... the turd star ... :-O

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "raydan" <nospamcom> wrote in message news:uq7a##phx.gbl... 

    Of course you do, he plays for Toronto. :-(
    raydan Guest

  8. #8

    Default Re: Count problem

    Hey, after we won the Stanley Cup in 1967 (and I had to go to Expo 67 to see it) I've pretty much lost interest in the NHL. That was the last year we had a good team - and there were only 6 teams in the league. Now, I can't name one Toronto Maple Leaf - or is that Maple Laff. Haven't been to an NHL game since the 70's.

    I always wanted to go to the Forum - not just to watch a game but to hear Roger Doucet sing the national anthem. Those were the days. :-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "raydan" <nospamcom> wrote in message news:#phx.gbl... 

    That's "la troisième étoile",
    .... and "shame on me", I can't even name one Canadiens player.

    Let's put an end to this thread.
    There are 3 things that should not be discussed in this newsgroup.
    Sports, religion and ANSI standards.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Oh, here we go. I take it you're a Canadiens fan? ;-) La troiseme etoile ... the turd star ... :-O

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "raydan" <nospamcom> wrote in message news:uq7a##phx.gbl... 

    Of course you do, he plays for Toronto. :-(

    Tom Guest

Similar Threads

  1. count problem
    By Jonathan Grenier in forum MySQL
    Replies: 6
    Last Post: December 21st, 09:14 AM
  2. Count Record Problem
    By patelajk in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 17th, 03:28 PM
  3. Count ()
    By Angelosalsa in forum Coldfusion Database Access
    Replies: 0
    Last Post: February 17th, 03:57 AM
  4. small count problem
    By Lasse Edsvik in forum ASP Database
    Replies: 12
    Last Post: January 7th, 07:50 PM
  5. COUNT(*)
    By Craig Roberts in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139