CODEWITHSUNDEEP
sqloracle11g
Odatas
Odatas

Reputation: 178

Count rows with specific entry for every distinct entry of another row

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.

Upvotes: 2

Views: 77

Answers (4)

Filip Kubiak
Filip Kubiak

Reputation: 31

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.

Upvotes: 3

LukStorms
LukStorms

Reputation: 29677

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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;

Upvotes: 2

jradich1234
jradich1234

Reputation: 1425

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

Upvotes: 0

Related Questions