February 1, 2016 at 8:39 pm
I have a sports team stat database, where each record contains a teams performance (win , loss, against- the- spread win, against-the-spread loss, if the game went over the total, if the game went under the total, etc.) , and using aliases to tag each line.
--
select team,sum(ats) as W,sum(atsloss) as L,sum(O) as Ov, sum(U) as Un,
sum(case when line < 0 and ats = 1 then 1 else 0 end) as favW,
sum(case when line < 0 and atsloss = 1 then 1 else 0 end) as favL
from teamlogs group by team
..but i am trying to evaluate a team's total wins in their past 5 games, and sql doesnot allow me to do the aggregate function on a subquery:
SUM(case when ats =1 and gamedate > (select top 5 gamedate from teamlogs) then 1 else 0 end) as top5
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
so how would i check for a team's last 5 games from the present getdate() ??
??
Archy
February 1, 2016 at 9:22 pm
This is untested air code. But, it should be close enough to get you pointed in a direction that should work.
;with cte as
(
Select team, ats, atsloss, O, U, line,
row_number() over(partition by team order by gamedate desc) rowNum
from teamLogs
)
select team,sum(ats) as W,sum(atsloss) as L,sum(O) as Ov, sum(U) as Un,
sum(case when line < 0 and ats = 1 then 1 else 0 end) as favW,
sum(case when line < 0 and atsloss = 1 then 1 else 0 end) as favL
from cte
where rowNum <= 5
group by team
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 1, 2016 at 9:23 pm
the easiest way is to sort in descending order (and maybe filtering out unplayed games). Something like:
-- top 4 could be ANY number...
SELECT TOP 4 *
FROM (
SELECT 'W' As result, '1/1/2016' AS game_date
UNION ALL
SELECT 'L', '1/2/2016'
UNION ALL
SELECT 'L', '1/3/2016'
UNION ALL
SELECT 'W', '1/4/2016'
UNION ALL
SELECT 'L', '1/5/2016'
) x
ORDER BY game_date ASC;
If you were trying to do a running win/loss record, you'd do something like
CASE WHEN result = 'W' THEN 1 AS Wins,
CASE WHEN result = 'L' THEN 1 AS Losses
and then you could do a running total on those.
SUM(Wins) OVER (PARTITION BY Team ORDER BY GameDate)
February 1, 2016 at 9:52 pm
Thanks for help, but the cte would need to just evaluate a teams wins in the past 5 games, and the entire query/stored procedure would need to include the teams performance for ALL the games, too, in the table/.database. So how would I include the row with just the team's last 5 games performance?
Thanks
Arch
February 1, 2016 at 9:58 pm
If you would post CREATE TABLE, INSERT statements and your desired results based on the test data you will have a much better chance of getting a working solution meets your requirement.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 10:09 pm
CREATE TABLE [dbo].[teamLogs](
[logid] [int] IDENTITY(1,1) NOT NULL,
[gmweek] [varchar](50) NULL,
[gmdate] [datetime2](7) NULL,
[team] [char](10) NOT NULL,
[opp] [char](10) NULL,
[home] [bit] NULL,
[line] [numeric](18, 1) NULL,
[pts] [int] NULL,
[opppts] [int] NULL,
[diffy] [numeric](18, 2) NULL,
[ATS] [int] NULL,
[SU] [int] NULL,
[linetotal] [numeric](18, 1) NULL,
[SULOSS] [int] NULL,
[backtoback] [bit] NULL,
[O] [int] NULL,
[int] NULL,
[tid] [int] NULL,
[ATSloss] [int] NULL,
CONSTRAINT [teamLogs_i_PrimaryKey] PRIMARY KEY CLUSTERED
(
[logid] ASC
WinsLast5games would be an aliased field that would be in this lineup:
team W L Ov Un WinsLast5games
STL 11 5 6 5 4
February 1, 2016 at 11:15 pm
archyya (2/1/2016)
CREATE TABLE [dbo].[teamLogs]etc . . .
WinsLast5games would be an aliased field that would be in this lineup:
team W L Ov Un WinsLast5games
STL 11 5 6 5 4
How about some sample data for the table?
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 2, 2016 at 12:16 am
team W L Ov Un favW favL _1to3 _1to31 _3hto7 _3hto71 _7hto10 _7hto101 _7hto102 _7hto103 _10h _10h1 PtsFor PtsVs
atl 5 5 4 6 5 5 2 2 0 1 2 2 2 2 1 0 103 97
bos 3 6 8 1 3 4 1 3 1 0 1 1 1 1 0 0 107 105
here's an NBA example (excuse the formatting) , but here I would also like to include a WINSlast5games column that calculates how many wins a team has in last 5 contests
HTH
Archy
February 2, 2016 at 7:21 am
archyya (2/2/2016)
team W L Ov Un favW favL _1to3 _1to31 _3hto7 _3hto71 _7hto10 _7hto101 _7hto102 _7hto103 _10h _10h1 PtsFor PtsVsatl 5 5 4 6 5 5 2 2 0 1 2 2 2 2 1 0 103 97
bos 3 6 8 1 3 4 1 3 1 0 1 1 1 1 0 0 107 105
here's an NBA example (excuse the formatting) , but here I would also like to include a WINSlast5games column that calculates how many wins a team has in last 5 contests
HTH
Archy
Is this supposed to be the desired output? Can you post some sample data as insert statements for your table that would produce this as the expected output? As is stands right now you have several people trying to help but the question is so vague we can barely guess at what you want. Help us by providing the details of the data and we can help you find a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2016 at 2:45 pm
Give this query a try: I couldn't test it as I don't have data:
SELECT team,sum(ats) as W,sum(atsloss) as L,sum(O) as Ov, sum(U) as Un,
sum(case when line < 0 and ats = 1 then 1 else 0 end) as favW,
sum(case when line < 0 and atsloss = 1 then 1 else 0 end) as favL
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY team ORDER BY gmdate DESC) AS row_num
FROM teamLogs
) AS derived
WHERE row_num BETWEEN 1 AND 5
GROUP BY team
--ORDER BY team
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 3, 2016 at 4:39 pm
Try something like this:
Select
tl.team,
Sum (wins.ats) as top5
From
dbo.teamLogs tl
Outer Apply
(
Select
Top 5 tl1.ats
From
dbo.teamLogs tl1
Where
tl1.team = tl.team
Order By
tl1.gamedate DESC
) as wins
Group by
tl.team
February 3, 2016 at 5:16 pm
Thanks for help, all who applied. I shifted gears on this portion of the app a little bit, if I need to ask more questions (with pertinent info DDL, insert data , etc.) on this topic will let this thread know.
Archy
February 3, 2016 at 7:50 pm
ScottPletcher (2/2/2016)
Give this query a try: I couldn't test it as I don't have data:
SELECT team,sum(ats) as W,sum(atsloss) as L,sum(O) as Ov, sum(U) as Un,
sum(case when line < 0 and ats = 1 then 1 else 0 end) as favW,
sum(case when line < 0 and atsloss = 1 then 1 else 0 end) as favL
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY team ORDER BY gmdate DESC) AS row_num
FROM teamLogs
) AS derived
WHERE row_num BETWEEN 1 AND 5
GROUP BY team
--ORDER BY team
Slight change to the code would give what's requested:
WINSlast5games
SELECT team,sum(ats) as W,
sum(CASE WHEN row_num BETWEEN 1 AND 5 THEN ats ELSE 0 END ) as WINSlast5games ,
...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY team ORDER BY gmdate DESC) AS row_num
FROM teamLogs
) AS derived
GROUP BY team
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply