Ytukyg

So my task is to calculate some simple KPIs.

I have already accumulated a view with all the data I need.

 Year_CW    Is Started      Needs Help

 -------------------------------------

 2018/45       0              1

 2018/43       1              1

 2018/45       0              1

 2018/42       1              0

 2018/45       0              1

 2018/45       1              1

 2018/41       0              1

 2018/43       0              0

 2018/45       1              1

 2018/45       0              0

I then wrote the following query:

SELECT DISTINCT YEAR_CW 

FROM TestView

ORDER BY YEAR_CW DESC

Which returns this

Year_CW

--------

2018/45

2018/44

2018/43

2018/42

I would now like to count for each Year_CW how often there is a 1 and how often there is a zero for both of the other rows. This may be a fairly simple question but I'm just starting with SQL and I really don't know what the keyword is for a query based on an outer query.

The other queries would be

Select Count(Is Started)

from Testview

Where Is Started = 1

And so on for the others. But I really don't know how to put them together and base them on the first query.

Thanks for your help.

You seems want conditional aggregation :

select Year_CW,

       sum(case when col = 1 then 1 else 0 end) as one_count,

       sum(case when col = 0 then 1 else 0 end) as zero_count

from (select Year_CW, IsStarted as col

      from TestView tv

      union all

      select Year_CW, NeedsHelp

      from TestView tv

     ) tv

group by Year_CW

order by Year_CW desc;

select Year_CW

, sum(case when Is_Started = 1 then 1 end) as Is_Started_1

, sum(case when Is_Started = 0 then 1 end) as Is_Started_0

, sum(case when Needs_Help = 1 then 1 end) as Needs_Help_1

, sum(case when Needs_Help = 0 then 1 end) as Needs_Help_0

from Test_View 

group by Year_CW

So how I did it is I created 4 new fields for you. First one is giving value ‘1’ to every field where ‘Is_Started = 1’ and then I sum the instances. I did the same for 0 values and another two fields for values 1 and 0 for ‘Needs_ Help’ column. I believe this will give you your desired result.

So, if I'm understanding the question correctly you're just looking for the SUM of the additional two columns GROUP BY the Year_CW field. That would be the following.

SELECT Year_CW, SUM([Is Started]), SUM([Needs Help])

FROM TestView

GROUP BY Year_CW

If it's only 0 or 1, then both 0 or 1 can be summed up.

SELECT YEAR_CW, 

SUM("Is Started") AS TotalStarted,

SUM(1 - "Is Started") AS TotalNotStarted,

SUM("Needs Help") AS TotalNeedsHelp,

SUM(1 - "Needs Help") AS TotalNoHelpNeeded

FROM TestView

GROUP BY YEAR_CW

ORDER BY YEAR_CW DESC