I have a table like so:
| Year | Stage | Participant |
|---|---|---|
| 2000 | 1 | Peter |
| 2000 | 1 | John |
| 2000 | 1 | Daniel |
| 2000 | 2 | Peter |
| 2000 | 2 | John |
| 2000 | 2 | Steve |
| 2000 | 3 | Daniel |
| 2000 | 3 | John |
| 2001 | 1 | Peter |
| 2001 | 1 | John |
| 2001 | 1 | Daniel |
| 2001 | 2 | Peter |
| 2001 | 2 | Steve |
| 2001 | 3 | Daniel |
| 2001 | 3 | John |
| 2001 | 4 | Peter |
| 2001 | 4 | John |
| 2001 | 4 | Daniel |
And what I need is to count the Participants in the first and last Stage and sort by year.
So far I got this
SELECT year, stage, Count(Participant) as Participants FROM table
WHERE Stage=1 or Stage=????
GROUP BY Stage
Sort by Year
The problem is that each year has different amount of stages so I'm having problems on how to find and set as a parameter the LAST stage of each year
The output should look like this:
| Year | Stage | Participants |
|---|---|---|
| 2000 | 1 | 3 |
| 2000 | 3 | 2 |
| 2001 | 1 | 3 |
| 2001 | 4 | 3 |
CodePudding user response：
This should work for both MySQL & SQL Server:
WITH CTE AS(
SELECT Year,MIN(Stage) AS FirstStage, MAX(Stage) AS LastStage
FROM Table1
GROUP BY Year
)
SELECT T.Year,T.Stage,COUNT(Participant) AS Participants
FROM Table1 T
JOIN CTE ON T.Year = CTE.Year AND (T.Stage = CTE.FirstStage OR T.Stage = CTE.LastStage)
GROUP BY T.Year,T.Stage
Output:
| Year | Stage | Participants |
|---|---|---|
| 2000 | 1 | 3 |
| 2000 | 3 | 2 |
| 2001 | 1 | 3 |
| 2001 | 4 | 3 |
See this db<>fiddle
CodePudding user response：
Using your existing query and just filling in "the blanks" you can use a correlated subquery
select year, stage, Count(*) as Participants
from t
where Stage = 1
or Stage = (select Max(stage) from t t2 where t2.year = t.year)
group by Stage, Year
order by year
This is likely to be the most performant but other variations are possible, such as using window functions:
select distinct year, stage, Participants
from (
select Year, stage,
First_Value(stage) over(partition by year order by stage) Fstage,
First_Value(stage) over(partition by year order by stage desc) Lstage,
Count(*) over(partition by year, stage) Participants
from t
)t
where stage in (Fstage, Lstage)