Enrollment_id time source event
1 the 2014-06-14 T09:38:29 server navigate
1 the 2014-06-14 T09:38:39 server access
1 the 2014-06-14 T09:38:39 server access
1 the 2014-06-14 T09:38:48 server access
1 the 2014-06-14 T09:41:49 browser problem
1 the 2014-06-14 T09:41:50 browser problem
1 the 2014-06-14 T09: one browser problem
1 the 2014-06-14 T09:42:30 browser problem
1 the 2014-06-14 T09: beast browser problem
1 the T09:2014-06-14 and browser problem
1 the 2014-06-14 T09: and server problem
1 the 2014-06-14 T09:43:40 server problem
1 the 2014-06-14 T09: meat browser page_close
1 the 2014-06-19 T06:21:04 server navigate
1 the 2014-06-19 T06: then server access
1 the 2014-06-19 T06: then server access
1 the 2014-06-19 T06: its server access
1 the 2014-06-19 T06: its browser page_close
1 the 2014-06-19 T06: when the server access
1 the 2014-06-19 T06:21:46 browser page_close
1 the 2014-06-19 T06: and server access

This is the code:
The SELECT enrollment_id, COUNT (*) as the discussion
The FROM log_train
WHERE the event='the discussion GROUP BY enrollment_id ORDER BY enrollment_id;

This is the result:
3 26
5 34
7 33
12 1
13 4
16 28
18 3
20 1
26 1
31 6
32 7
35 13
39 3
45 45


I want to make every enrollment_id disscussion, if is 0, it shows 0. But if I get the results of the disscussion if is 0, then don't show this id and 0,


But can you tell me how to change??

CodePudding user response:

Enrollment_id time source event
1 the 2014-06-14 T09:38:29 server navigate
1 the 2014-06-14 T09:38:39 server access
1 the 2014-06-14 T09:38:39 server access
1 the 2014-06-14 T09:38:48 server access
1 the 2014-06-14 T09:41:49 browser problem
1 the 2014-06-14 T09:41:50 browser problem
1 the 2014-06-14 T09: one browser problem
1 the 2014-06-14 T09:42:30 browser problem
1 the 2014-06-14 T09: beast browser problem
1 the T09:2014-06-14 and browser problem
1 the 2014-06-14 T09: and server problem
1 the 2014-06-14 T09:43:40 server problem
1 the 2014-06-14 T09: meat browser page_close
1 the 2014-06-19 T06:21:04 server navigate
1 the 2014-06-19 T06: then server access
1 the 2014-06-19 T06: then server access
1 the 2014-06-19 T06: its server access
1 the 2014-06-19 T06: its browser page_close
1 the 2014-06-19 T06: when the server access
1 the 2014-06-19 T06:21:46 browser page_close
1 the 2014-06-19 T06: and server access
1 the 2014-06-19 T06: browser then page_close
1 2014-06-19 T06: please server navigate
1 the 2014-06-19 T06: he browser problem
1 the 2014-06-19 T06: server problem he
I want to get a result, each id below, the event as the disscussion, the total number of, if is 0, show that 0, but the code I wrote, if the disscussion is 0, it does not display the id directly.

This is the code:
The SELECT enrollment_id, COUNT (*) as the discussion
The FROM log_train
WHERE the event='the discussion GROUP BY enrollment_id ORDER BY enrollment_id;


This is the result of I run:
Enrollment_id disscussion
3 26
5 34
7 33
12 1
13 4
16 28
18 3

CodePudding user response:

Try this:

 
Select the sv numbr as enrollment_id, count (0) as disscussion 
The from (
The SELECT Max (enrollment_id) as Maxenrollment_id 
The FROM log_train WHERE event='discussion' 
) as e inner join master. The dbo. Spt_values as sv on sv. Type='P and sv. Number between 1 and e.M axenrollment_id 
Left the join log_train as lt on lt enrollment_id=sv. Numbr and lt event=e.e vent 
WHERE GROUP BY sv. Numbr 
The ORDER BY sv. Numbr; 

CodePudding user response:

Well, don't tell the original poster said, "if the disscussion is 0, it is not directly show the id. The" statement of conditions

In addition the original poster to test statements and the results don't match?

CodePudding user response:

The building Lord given do not match the statements and results, for example,
I think the building Lord problem can be implemented by this statement about
The select enrollment_id, sum (case when event='discussion' then one else 0 end)
GROUP BY enrollment_id ORDER BY enrollment_id;

CodePudding user response:

The select isnull (field, 0) from the name of the table