SQL query to add the number of occurrences for current year and all previous years as two numbers-CodePudding

I need help with the following: I have a table called Statuses which contains two columns period, status (this is an example)

year Status
--------------------
2021 enrolled
2022 enrolled
2021 enrolled 
2021 temp enrolled
2023 enrolled
2023 enrolled

What I need is a SQL query to return the number of enrolled for 2023 and the number of enrolled for all the previous years to 2023 as one number.

So my result should looks something like this:

year       status
------------------
2023         2
2021-2022    3

Will this be possible? Apologies as I am new to SQL and not sure if I formatted this question right either.

CodePudding user response：

We can use a union query here:

SELECT '2023' AS year, COUNT(*) AS cnt
FROM yourTable
WHERE year = 2023 AND Status = 'enrolled'
UNION ALL
SELECT '< 2023', COUNT(*)
FROM yourTable
WHERE year < 2023 AND Status = 'enrolled';

But actually, a more typical and also more performant way to do this reporting would be to use conditional aggregation:

SELECT
    COUNT(CASE WHEN year = 2023 THEN 1 END) AS [2023],
    COUNT(CASE WHEN year < 2023 THEN 1 END) AS [prior_2023]
FROM yourTable
WHERE Status = 'enrolled';

This approach has the advantage of only requiring a single pass over your table.

CodePudding user response：

thanks the second solution works great but the result is in two columns, is there a way I can show the result in two rows like

year       status
2023         2
2021-2022    3

the solution at the moment return

row 2023 prior_23
1    0    3
2    2    0

CodePudding user response：

Group by max(year)(): fiddle


create table statuses(year int, status varchar(20));

insert into statuses(year, status)
values
(2021, 'enrolled'),
(2022, 'enrolled'),
(2021, 'enrolled'),
(2021, 'temp enrolled'),
(2023, 'enrolled'), --(2024, 'temp enrolled'),
(2023, 'enrolled');


select concat(cast(nullif(min(year), maxyear) as varchar(20)) '-', max(year)), sum(case when status = 'enrolled' then 1 else 0 end)
from
(
select *, max(year) over() as maxyear
from statuses
) as s
group by case when year = maxyear then 1 else 0 end, maxyear;

update statuses set status = 'temp enrolled';

select concat(cast(nullif(min(year), maxyear) as varchar(20)) '-', max(year)), sum(case when status = 'enrolled' then 1 else 0 end)
from
(
select *, max(year) over() as maxyear
from statuses
) as s
group by case when year = maxyear then 1 else 0 end, maxyear;