Group By SUM, statement

[vb89]

For the following code, the output displays two rows of data for some reason, it displays, team_name, punt_return, fair_catch, punt_ret_yd...any idea what is wrong with my code...thanks!

select team_name,

       sum(Punt_ret) as Punt_ret,

       nvl(punt_fair_catch, 0) as punt_fair_catch,

       sum(Punt_ret_yd) as Punt_ret_yd,

       case

         when season <= 1993 then

          '-'

         else

          to_char(sum(Punt_lng))

       end as punt_lng,

       case

         when season <= 1993 then

          '-'

         else

          Punt_ret_lng_is_td

       end as Punt_ret_lng_is_td,

       case

         when season <= 1993 then

          '-'

         else

          to_char(sum(Punt_ret_td))

       end as Punt_ret_td

  from customer_data.cd_football_retgame

 WHERE game_code = 765667

   and split_number = -1

   and Punt_ret > 0

   and team_id = 354

 GROUP BY team_id, team_id_1032, team_name, season, punt_fair_catch, punt_ret_lng_is_td

 

[vb89]

To clarify: It displays two rows of data for the columns mentioned above

 

[RiverGuy]

Your group by doesn't match your select. If you choose to display team_id, team_id_1032 and season, then you may see differences in the rows returned.

 

[vb89]

That still didn't do it, I did a groupby team_id, team_id_1032, season, team_name and still an output of two rows?

 

[bborissov]

NVL()?
There is no NVL() function in SQL Server, there is ISNULL() or COALESCE(). Also there is no to_char() function also. You should use CAST() or CONVERT()

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Could you post some example data and what you want as a result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[vb89]

yea sure, i have the following data:
i have team name , punt_ret , punt_fair_catch, punt_retyd, game_code, first_name, last_name...for the game code that i displayed above there is two rows of data with stats for two different players for the same game code, i want to display one row with: team name, punt_ret, punt_fair_catch, punt_retyd...and thats all

so here:

team name: hawks
punt_ret : 2
punt_fair_catch : 3
punt_retyd : 4
game_code: 1111
first_name: bob
last_name: smith

team name: hawks
punt_ret : 5
punt_fair_catch : 6
punt_retyd : 7
game_code: 1111
first_name: kevin
last_name: johnson

i want it to display:
team name: hawks
punt_ret : 7
punt_fair_catch : 9
punt_retyd : 11

 

[bborissov]

For that data you provided that is the query whch give you the result you want:

DECLARE @Test TABLE (TeamName varchar(200), punt_ret int, punt_fair_catch int, punt_retyd int, game_code int, first_name varchar(200),  last_name varchar(200))
INSERT INTO @Test VALUES('hawks', 2, 3, 4, 1111,'bob','smith')
INSERT INTO @Test VALUES('hawks', 5, 6, 7, 1111, 'kevin','johnson')

SELECT TeamName,
       SUM(punt_ret)        AS punt_ret,
       SUM(punt_fair_catch) AS punt_fair_catch,
       SUM(punt_retyd)      AS punt_retyd
FROM @Test
GROUP BY TeamName

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[vb89]

a appreciate the response bborissov but i didn't really want to hard code anything, because this code for a stored proc and i may need to use it again for other data

 

[bborissov]

Where you see I hard coded something?
The first part where I declare a table variable and insert some data is just to prepare the data for the query, you don't need this, just change @Temp in the query with your real table name.
So let me put some comments in my example:

--- Preparing Test data YOU don't need this:
DECLARE @Test TABLE (TeamName varchar(200), punt_ret int, punt_fair_catch int, punt_retyd int, game_code int, first_name varchar(200),  last_name varchar(200))
INSERT INTO @Test VALUES('hawks', 2, 3, 4, 1111,'bob','smith')
INSERT INTO @Test VALUES('hawks', 5, 6, 7, 1111, 'kevin','johnson')
--- END of preparing

SELECT TeamName,
       SUM(punt_ret)        AS punt_ret,
       SUM(punt_fair_catch) AS punt_fair_catch,
       SUM(punt_retyd)      AS punt_retyd
FROM @Test -- Change this to your real table name
GROUP BY TeamName

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[vb89]

yea nm, I got a bit confused, there...works fine, thanks