1
00:00:00,430 --> 00:00:02,930
Since we're tracking
our bird sightings and

2
00:00:02,930 --> 00:00:07,010
conservation status, let's write
a query to see how many sightings

3
00:00:07,010 --> 00:00:11,800
have been reported of birds that
are at risk of becoming endangered.

4
00:00:11,800 --> 00:00:14,280
Make sure you've got your birds list and
your C# REPL.

5
00:00:15,340 --> 00:00:19,623
First, let's check out the different
conservation statuses the birds have.

6
00:00:19,623 --> 00:00:27,787
for statuses equals birds.Select,

7
00:00:27,787 --> 00:00:35,540
b goes to b.Conservationstatus and

8
00:00:35,540 --> 00:00:41,390
then I call Distinct so
I remove the duplicates statuses.

9
00:00:42,910 --> 00:00:46,400
So we've got least concerned,
endangered, vulnerable,

10
00:00:46,400 --> 00:00:49,320
near threatened and critically endangered.

11
00:00:49,320 --> 00:00:53,820
We only want to concern ourselves
with the endangered statuses So,

12
00:00:53,820 --> 00:00:57,130
why don't we take out least concerned and
near threatened.

13
00:00:57,130 --> 00:01:00,866
Let's reassign it the same variable but
without those statuses.

14
00:01:00,866 --> 00:01:07,147
So statuses = statuses.Where

15
00:01:07,147 --> 00:01:11,797
s goes to s is not equal to

16
00:01:11,797 --> 00:01:16,217
"LeastConcern" and

17
00:01:16,217 --> 00:01:23,900
s is not equal to "NearThreatened".

18
00:01:27,960 --> 00:01:33,170
Okay, so now our statuses has
only what we want to deal with.

19
00:01:33,170 --> 00:01:35,500
We'll need to access
the sightings of birds and

20
00:01:35,500 --> 00:01:37,940
then only get those that
are in our list of statuses.

21
00:01:37,940 --> 00:01:42,360
I bet there are a lot of
ways we can achieve this.

22
00:01:42,360 --> 00:01:46,910
The first thing I think of when I've
got two different collections is a join

23
00:01:46,910 --> 00:01:50,160
we can join our list of birds
to the status as a variable

24
00:01:50,160 --> 00:01:52,784
then we'll only get the birds
that have that status.

25
00:01:52,784 --> 00:01:57,997
FarendangeredSightings equals

26
00:01:57,997 --> 00:02:03,213
start with birds.join statuses

27
00:02:05,310 --> 00:02:06,780
I'll do a new line here.

28
00:02:08,000 --> 00:02:12,399
So our outer key would be birds b goes to

29
00:02:12,399 --> 00:02:18,980
b.ConservationStatus facial and

30
00:02:18,980 --> 00:02:23,597
then our inner key selector
From our statuses list,

31
00:02:23,597 --> 00:02:26,730
s => s, cuz it's just strings.

32
00:02:29,280 --> 00:02:30,450
All right, now for our result.

33
00:02:31,620 --> 00:02:36,045
I'll just use the two input parameters (b,

34
00:02:36,045 --> 00:02:43,670
s) => I'm gonna create
a anonymous type here

35
00:02:44,690 --> 00:02:50,586
new status equals s and

36
00:02:50,586 --> 00:02:59,910
birds equals b, okay.

37
00:02:59,910 --> 00:03:01,440
Let's see what that gives us.

38
00:03:01,440 --> 00:03:02,550
EndangeredSightings.

39
00:03:03,780 --> 00:03:08,590
We've got lists of birds, but
now we need the sightings.

40
00:03:08,590 --> 00:03:11,500
Well we could actually put in sightings

41
00:03:11,500 --> 00:03:15,750
inside our anonymous objects to get
the sightings instead of the birds.

42
00:03:15,750 --> 00:03:21,670
So I'm gonna use my Up arrow key to get
most of the query back so I can reuse it.

43
00:03:23,240 --> 00:03:25,810
B goes to B dot conservation status.

44
00:03:27,360 --> 00:03:33,790
Then S goes to S but
here I'm going to do B S

45
00:03:35,040 --> 00:03:38,420
goes to new we'll keep the statuses.

46
00:03:38,420 --> 00:03:39,520
We like statuses.

47
00:03:40,720 --> 00:03:44,790
Equals s but
this time I'm gonna say Sightings

48
00:03:44,790 --> 00:03:49,750
equals b.Sightings.

49
00:03:49,750 --> 00:03:55,710
Okay, let see what that gives us,
endangered sightings.

50
00:03:57,600 --> 00:04:01,030
So we've got one element for each bird.

51
00:04:01,030 --> 00:04:04,890
We need to group them and
then get a total count for each status.

52
00:04:04,890 --> 00:04:07,330
So let's group by status.

53
00:04:10,499 --> 00:04:14,274
So var endangered sightings.

54
00:04:14,274 --> 00:04:16,536
Actually how about I clear the console?

55
00:04:21,655 --> 00:04:25,205
Okay, and then I'm gonna use my up
arrow key again to get my history.

56
00:04:25,205 --> 00:04:32,761
var endangeredSightings
= birds.Join(statuses,.

57
00:04:32,761 --> 00:04:33,700
Yep, looks good.

58
00:04:33,700 --> 00:04:36,600
b => b.ConservationStatus and then.

59
00:04:36,600 --> 00:04:37,730
S goes to S.

60
00:04:37,730 --> 00:04:43,708
But then I'll do b, s goes to

61
00:04:43,708 --> 00:04:48,900
new status.

62
00:04:48,900 --> 00:04:50,660
Actually we're gonna do the same thing.

63
00:04:50,660 --> 00:04:51,900
I coulda used my arrow key.

64
00:04:51,900 --> 00:04:53,430
But that's okay.

65
00:04:53,430 --> 00:05:00,095
Status = s and then Sightings

66
00:05:00,095 --> 00:05:05,960
= b.Sightings then I'm

67
00:05:05,960 --> 00:05:11,292
going to call .GroupBy

68
00:05:11,292 --> 00:05:17,969
b goes to b.Status because,

69
00:05:20,315 --> 00:05:25,531
Our anonymous type here, the property
is status so we're gonna group by that.

70
00:05:25,531 --> 00:05:31,125
Okay, and endangeredSightings.

71
00:05:31,125 --> 00:05:32,240
All right.

72
00:05:32,240 --> 00:05:37,330
Well it looks like our statuses are there,
and our sightings Okay we do have

73
00:05:37,330 --> 00:05:43,400
a group it's a little hard to see like
this so let's do that same query again.

74
00:05:43,400 --> 00:05:45,079
I'm gonna clear the console.

75
00:05:48,023 --> 00:05:53,286
All right, endangeredSightings b goes to

76
00:05:53,286 --> 00:05:59,135
b.ConservationStatus s
goes to s then that but

77
00:05:59,135 --> 00:06:04,252
then, instead of returning sightings,

78
00:06:04,252 --> 00:06:09,970
let's do a select here
.Select b goes to new.

79
00:06:09,970 --> 00:06:16,140
Then we'll do Status = b.Key

80
00:06:16,140 --> 00:06:21,050
which should be our status, right,
because we just did the GroupBy Status.

81
00:06:21,050 --> 00:06:25,650
Next it's key and then the Sightings

82
00:06:27,010 --> 00:06:31,170
is going to be equal to the b.Sum and

83
00:06:31,170 --> 00:06:36,410
then s goes to s.Sightings .Count.

84
00:06:36,410 --> 00:06:41,260
So we're gonna get the sum of the count of
the sightings for each grouping by status.

85
00:06:42,870 --> 00:06:47,910
Then we need to curly brace
to end our anonymous type and

86
00:06:47,910 --> 00:06:52,980
then close the Select method and Okay,

87
00:06:52,980 --> 00:06:57,420
let's see what we get
now endangeredsightings.

88
00:06:59,060 --> 00:07:00,300
All right, great.

89
00:07:00,300 --> 00:07:03,110
We've got 14 sightings
of endangered birds.

90
00:07:03,110 --> 00:07:06,880
29 are vulnerable and
12 of critically endangered.

91
00:07:08,220 --> 00:07:09,622
Looks like I misspelled endangered.

92
00:07:09,622 --> 00:07:11,209
[LAUGH]