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
teamWLOvUnfavWfavL_1to3_1to31_3hto7_3hto71_7hto10_7hto101_7hto102_7hto103_10h_10h1PtsForPtsVs
atl554655220122221010397
bos3681341310111100107105
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)
teamWLOvUnfavWfavL_1to3_1to31_3hto7_3hto71_7hto10_7hto101_7hto102_7hto103_10h_10h1PtsForPtsVsatl554655220122221010397
bos3681341310111100107105
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) A socialist is someone who will give you the shirt off *someone else's* back.
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
July 3, 2023 at 6:03 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply