Stats Query (Select, Join, Group, Rollup?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stats Query (Select, Join, Group, Rollup?)

Author

Topic

jbraun
Starting Member

10 Posts

Posted - 2003-05-22 : 12:26:14

Hello,
I run a website for our local baseball association. Coaches input stats every week for their games, and Hitter stats are stored in a single table for each player for every game. They have a PlayerID, TeamID, and GameID plus all the stats for each record.

I am trying to build a query that would give me the "Season Stats" for each team, summing up each player's stats from the HitterStats table.

Any assistance would be appreciated.

JB

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-22 : 12:32:33

Can you please post the DDL of you table (create table statement) and some sample data and an example of the rowset you'd like the query to produce? How do you want to aggregate the season stats? Averages? Min? Max? Standard Deviations?

Jay White
{0}

jbraun
Starting Member

10 Posts

Posted - 2003-05-22 : 18:29:13

THanks Jay. Here is the layout of the table, some sample data, and the Final output that I would like:

CREATE TABLE [HitterStats] (
[StatID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NULL ,
[PlayerID] [int] NULL ,
[G] [int] NULL ,
[AB] [int] NULL ,
[R] [int] NULL ,
[H] [int] NULL ,
[RBI] [int] NULL ,
[1B] [int] NULL ,
[2B] [int] NULL ,
[3B] [int] NULL ,
[HR] [int] NULL ,
[TB] [int] NULL ,
[BB] [int] NULL ,
[K] [int] NULL ,
[SB] [int] NULL ,
[CS] [int] NULL ,
[SAC] [int] NULL ,
[HP] [int] NULL ,
[CreateDate] [datetime] NULL ,
CONSTRAINT [PK_GameStats] PRIMARY KEY CLUSTERED
(
[StatID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

SAMPLE DATA:
StatID SchID PlayID G AB R H RBI 1B 2B 3B HR TB BB K SB CS SAC HP CreateDate
13 29 8 1 5 0 1 1 1 0 0 0 1 0 2 0 0 0 0 2003-04-30 00:51:45.810
17 29 18 1 4 0 1 0 1 0 0 0 1 0 1 0 0 0 0 2003-04-30 00:53:12.570
23 30 8 1 6 3 3 0 2 1 0 0 0 0 0 1 0 0 0 2003-05-04 22:54:59.890
26 30 18 1 5 1 1 2 1 0 0 0 0 0 0 0 0 0 0 2003-05-04 22:56:13.143

OUTPUT
-PlayerID, Sum of each categories above

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-22 : 22:26:30

SELECT PlayerID, SUM(ColumnNameHere), SUM(Column2HERE)...etc..
FROM HitterStats
GROUP BY PlayerID

jbraun
Starting Member

10 Posts

Posted - 2003-05-23 : 01:58:57

That's a lot easier than I thought it would be. Can we take it a step further, and possibly add a row that would Total each different stat for the entire team. Maybe that is where the ROLLUP function comes into play?

In addition, I was calcuating the average in my VB code, but now that I think about it, it would be nice to do the calcuation in the stored proc so I could sort by Batting Average. The problem I am having is when I take Hits divided by At Bats (H/AB), It will return 0 except where a batter is batting %1000 (1.000), then it returns 1. I'm not sure what is going wrong there. And in the case where there are no hits or no at bats registered, I just wanted to return .000.

Thanks for all the help, I really appreciate it!

Edited by - jbraun on 05/23/2003 02:06:05

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-23 : 10:32:40

TRY

CAST(H AS Real)/CAST(AB AS Real)

Your problem is rounding due to integer division.

SELECT
CASE WHEN GROUPING(PlayerID) = 1 THEN 'ALL'
ELSE ISNULL(CAST(PlayerID AS NVARCHAR), 'UNKNOWN')
END AS PlayerID,
AVG(CAST(H AS Real)/CAST(AB AS Real)),
SUM(ColumnNameHere),
SUM(Column2HERE)...etc..
FROM HitterStats
GROUP BY PlayerID WITH ROLLUP

jbraun
Starting Member

10 Posts

Posted - 2003-05-23 : 10:53:54

The CAST statement seems to spit out a 'Divide by zero error encountered.' I understand that some of these players have 0 At Bats and 0 Hits which would throw that ... However, my SQL skills are so basic that I've never tried to get around this by doing any error checking in the Stored Proc. I'm not sure how I can bypass this type of error and spit out some default value for it.

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-23 : 11:01:47

You could probably change it to

case
when ab > 0 then AVG(CAST(H AS Real)/CAST(AB AS Real))
else 0 end as average

or something similar. I've not tested it though.

-------
Moo.

jbraun
Starting Member

10 Posts

Posted - 2003-06-06 : 01:18:36

I have the query mostly figured out ... It seems that the GROUP BY statement in conjunction with the CASE statement is giving me problems. The query seems to be working except that each playerID has about 8-10 entries. I need this rolled up to a single entry per player ID. Here is what I have, and I have included my comments where I am having problems:

SELECT

CASE WHEN GROUPING(PlayerID) = 1 THEN 'TOTAL'
ELSE ISNULL(CAST(PlayerID AS nvarchar), 'UNKNOWN')
END AS PlayerID,

/*
When this CASE statement is enabled with the Group By, it produces this error:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'HitterStats.AB' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

CASE WHEN AB <> 0 THEN CONVERT(decimal(6,3),CONVERT(real, SUM(H)) / CONVERT(real, SUM(AB)))
ELSE '0.00'
END AS 'BattingAvg',
*/

SUM(AB) AS 'TotalAB',
SUM(H) AS 'TotalH'
FROM HitterStats

--Then if I enable this, I get like 8 rows for each PlayerID and so they don't rollup correctly
--GROUP BY PlayerID, AB, H WITH ROLLUP

GROUP BY PlayerID WITH ROLLUP
ORDER BY PlayerID ASC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-06 : 07:49:18

This should work:

CASE WHEN SUM(AB) <> 0 THEN CONVERT(decimal(6,3),CONVERT(real, SUM(H)) / CONVERT(real, SUM(AB)))
ELSE 0.0

Does that make sense as to why you need that? If not, let me know -- it's a subtle thing but a key thing to understand when dealing with aggregates ...

EDIT: also, get rid of the quotes around the 0.0. You want to return a number, not a string.

- Jeff

Edited by - jsmith8858 on 06/06/2003 07:50:07

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-06 : 07:52:59

Oh, one more thing: my advice is to NOT calculate both the player and team totals in the same query -- write a seperate one for each.

When you want team totals, you use the team query, when you want player totals use the players query.

If you want to list all of the players on the team and then as a "team footer" provide the team's totals, do it at the presentation layer -- much easier that way. Don't try to force SQL Server to do presentation for you.

How is this data ultimately being viewed? (I.e., an ASP page, a report, in Access or Excel, etc)

- Jeff

Edited by - jsmith8858 on 06/06/2003 07:53:58

jbraun
Starting Member

10 Posts

Posted - 2003-06-06 : 08:52:43

Thanks Jeff. SUMming the AB completely fixes the problem. I wrote several functions outside to do what that does, then I called it from a query, but I really wanted to self contain it if possible.

I am using this tabulation on a baseball website for a stats, so it will be done through ASP/ASP.NET pages.

Is the recommended way to break this up into two queries? One that would give individual player totals and one that would give team totals?

jbraun
Starting Member

10 Posts

Posted - 2003-06-06 : 09:02:55

Jeff, can you also explain a little more in-depth the deal with aggregates? This has come back to bite me several times, I just don't think I quite understand it.

Thanks,
JB

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-06 : 11:17:38

quote:

Jeff, can you also explain a little more in-depth the deal with aggregates? This has come back to bite me several times, I just don't think I quite understand it.

Thanks,
JB

I think that Jeff is mentioning that you make sure you handle division by 0...

Aggregates are simply (BOL):

Aggregate functions
(such as SUM, AVG, COUNT, COUNT(*), MAX, and MIN)
generate summary values in query result sets.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources