Query Prosentase Kehadiran

1. DROP TABLE IF EXISTS provinsi;
2. CREATE TABLE IF NOT EXISTS provinsi (
3. id INT NOT NULL,
4. nama tinytext NOT NULL,
5. PRIMARY KEY (id)
6. );
8. INSERT INTO provinsi (id, nama) VALUES
9. (1,‘Aceh’),
10. (2,‘Sumatera Utara’),
11. (3,‘Sumatera Barat’),
12. (4,‘Riau’),
13. (5,‘Jambi’),
14. (6,‘Sumatera Selatan’),
15. (7,‘Bengkulu’),
16. (8,‘Lampung’),
17. (9,‘Kepulauan Bangka Belitung’),
18. (10,‘Kepulauan Riau’),
19. (11,‘DKI Jakarta’),
20. (12,‘Jawa Barat’),
21. (13,‘Jawa Tengah’),
22. (14,‘DI Yogyakarta’),
23. (15,‘Jawa Timur’),
24. (16,‘Banten’),
25. (17,‘Bali’),
26. (18,‘Nusa Tenggara Barat’),
27. (19,‘Nusa Tenggara Timur’),
28. (20,‘Kalimantan Barat’),
29. (21,‘Kalimantan Tengah’),
30. (22,‘Kalimantan Selatan’),
31. (23,‘Kalimantan Timur’),
32. (24,‘Kalimantan Utara’),
33. (25,‘Sulawesi Utara’),
34. (26,‘Sulawesi Tengah’),
35. (27,‘Sulawesi Selatan’),
36. (28,‘Sulawesi Tenggara’),
37. (29,‘Gorontalo’),
38. (30,‘Sulawesi Barat’),
39. (31,‘Maluku’),
40. (32,‘Maluku Utara’),
41. (33,‘Papua Barat’),
42. (34,‘Papua’);
44. DROP TABLE IF EXISTS peserta;
45. CREATE TABLE IF NOT EXISTS peserta(
46. id INT AUTO_INCREMENT,
47. nama VARCHAR(30),
48. provinsi VARCHAR(30),
49. PRIMARY KEY(id)
50. );
52. INSERT INTO peserta (nama,provinsi) VALUES
53. (“Zachery Singleton”,24),
54. (“Stewart Irwin”,13),
55. (“Aaron Hall”,34),
56. (“Eagan Francis”,32),
57. (“Dolan Carrillo”,8),
58. (“Victor Church”,7),
59. (“Zachary Moreno”,30),
60. (“Herman Jensen”,10),
61. (“Devin Brady”,29),
62. (“Paki Morton”,15),
63. (“Beck Gregory”,15),
64. (“Knox Sherman”,16),
65. (“Keefe Mercer”,15),
66. (“Kermit Walls”,19),
67. (“Marvin Cooper”,1),
68. (“Gary Davenport”,2),
69. (“Quinn Giles”,18),
70. (“Lev Sherman”,15),
71. (“Erasmus Travis”,31),
72. (“Herman Wagner”,18),
73. (“Maxwell Harvey”,26),
74. (“Jacob Cash”,11),
75. (“Addison Weiss”,15),
76. (“Marvin Patterson”,8),
77. (“Fletcher Lindsay”,2),
78. (“Nasim Spence”,9),
79. (“Ray York”,5),
80. (“Elvis Garrett”,30),
81. (“Dalton Shannon”,14),
82. (“Vance Hoffman”,18),
83. (“Leo Guzman”,20),
84. (“Odysseus Kane”,33),
85. (“Arden Wilder”,6),
86. (“Rudyard Dalton”,20),
87. (“Driscoll Cote”,11),
88. (“Brenden Morrow”,24),
89. (“Martin Meadows”,12),
90. (“Hayes Walter”,9),
91. (“Keith Stark”,29),
92. (“Kato Mosley”,10),
93. (“Stephen Dennis”,9),
94. (“Cyrus Chambers”,6),
95. (“Garrison Dyer”,14),
96. (“Aladdin King”,32),
97. (“Dieter Strong”,15),
98. (“Beau Pate”,7),
99. (“Len Raymond”,17),
100. (“Upton Bentley”,17),
101. (“Addison Giles”,20),
102. (“Channing Head”,10),
103. (“Hasad Willis”,1),
104. (“Kamal Avery”,29),
105. (“Ira Barker”,25),
106. (“Jermaine Schneider”,6),
107. (“Ethan Wall”,14),
108. (“Malachi Evans”,16),
109. (“Amir Francis”,12),
110. (“Daquan Vaughan”,13),
111. (“Kennedy Jefferson”,2),
112. (“Felix Marshall”,15),
113. (“Garrison Matthews”,3),
114. (“Fletcher Becker”,27),
115. (“Oliver Johns”,30),
116. (“Carlos Camacho”,29),
117. (“Joseph Christensen”,29),
118. (“Nicholas Howe”,16),
119. (“Timothy Walls”,31),
120. (“Hector Buckner”,33),
121. (“Buckminster Hancock”,24),
122. (“Derek Sawyer”,25),
123. (“Seth Buckley”,11),
124. (“Holmes Strickland”,7),
125. (“Neville Mayer”,32),
126. (“Allistair Holland”,23),
127. (“Perry Moran”,30),
128. (“Keefe Barr”,7),
129. (“Palmer Whitfield”,29),
130. (“Herman Campbell”,29),
131. (“Ali Jacobson”,9),
132. (“Francis Stevens”,14),
133. (“Lyle Coleman”,1),
134. (“Alec Navarro”,8),
135. (“Allistair Rivas”,17),
136. (“Ross Barron”,8),
137. (“Mufutau Glover”,13),
138. (“Harlan Small”,31),
139. (“Davis Fleming”,8),
140. (“Carl Sosa”,28),
141. (“Lester Puckett”,12),
142. (“Ronan Rhodes”,27),
143. (“Giacomo Walon”,28),
144. (“Lane Jacobson”,33),
145. (“Paul Mills”,9),
146. (“Tiger Shaffer”,22),
147. (“Josiah Henson”,22),
148. (“Darius Alston”,17),
149. (“August Estes”,24),
150. (“Chaney Chavez”,7),
151. (“Channing Chavez”,29),
152. (“Richard Bowen”,17);
155. DROP TABLE IF EXISTS pertemuan_detail;
156. CREATE TABLE IF NOT EXISTS pertemuan_detail(
157. id INT AUTO_INCREMENT,
158. parent_id INT,
159. peserta_id INT,
160. jam_hadir DATETIME,
161. PRIMARY KEY (id)
162. );
165. INSERT INTO pertemuan_detail (id, parent_id, peserta_id, jam_hadir) VALUES
166. (1, 1, 94, ‘2016-01-11 06:42:18’),
167. (2, 1, 69, ‘2016-01-11 06:47:48’),
168. (3, 1, 57, ‘2016-01-11 06:46:52’),
169. (4, 1, 50, ‘2016-01-11 06:35:52’),
170. (5, 1, 10, ‘2016-01-11 06:34:22’),
171. (6, 1, 79, ‘2016-01-11 06:34:39’),
172. (7, 1, 54, ‘2016-01-11 06:42:30’),
173. (8, 1, 95, ‘2016-01-11 06:36:14’),
174. (9, 1, 11, ‘2016-01-11 06:45:56’),
175. (10, 1, 73, ‘2016-01-11 06:46:51’),
176. (11, 1, 48, ‘2016-01-11 06:35:30’),
177. (12, 1, 39, ‘2016-01-11 06:49:12’),
178. (13, 1, 58, ‘2016-01-11 06:47:19’),
179. (14, 1, 51, ‘2016-01-11 06:48:28’),
180. (15, 1, 91, ‘2016-01-11 06:42:01’),
181. (16, 1, 42, ‘2016-01-11 06:34:39’),
182. (17, 1, 36, ‘2016-01-11 06:33:50’),
183. (18, 1, 46, ‘2016-01-11 06:45:14’),
184. (19, 1, 17, ‘2016-01-11 06:36:30’),
185. (20, 1, 3, ‘2016-01-11 06:44:10’),
186. (21, 1, 65, ‘2016-01-11 06:42:26’),
187. (22, 1, 74, ‘2016-01-11 06:34:32’),
188. (23, 1, 49, ‘2016-01-11 06:35:08’),
189. (24, 1, 60, ‘2016-01-11 06:37:38’),
190. (25, 1, 19, ‘2016-01-11 06:35:07’),
191. (26, 1, 30, ‘2016-01-11 06:38:48’),
192. (27, 1, 81, ‘2016-01-11 06:45:43’),
193. (28, 1, 5, ‘2016-01-11 06:33:16’),
194. (29, 1, 44, ‘2016-01-11 06:48:53’),
195. (30, 1, 29, ‘2016-01-11 06:38:43’),
196. (31, 1, 71, ‘2016-01-11 06:34:23’),
197. (32, 1, 24, ‘2016-01-11 06:41:00’),
198. (33, 1, 75, ‘2016-01-11 06:45:40’),
199. (34, 1, 18, ‘2016-01-11 06:40:03’),
200. (35, 1, 26, ‘2016-01-11 06:42:18’),
201. (36, 1, 28, ‘2016-01-11 06:45:41’),
202. (37, 1, 92, ‘2016-01-11 06:45:49’),
203. (38, 1, 86, ‘2016-01-11 06:40:10’),
204. (39, 1, 6, ‘2016-01-11 06:35:35’),
205. (40, 1, 14, ‘2016-01-11 06:40:35’),
206. (41, 1, 67, ‘2016-01-11 06:44:37’),
207. (42, 1, 55, ‘2016-01-11 06:44:48’),
208. (43, 1, 31, ‘2016-01-11 06:38:44’),
209. (44, 1, 9, ‘2016-01-11 06:43:55’),
210. (45, 1, 43, ‘2016-01-11 06:45:45’),
211. (46, 1, 84, ‘2016-01-11 06:34:42’),
212. (47, 1, 89, ‘2016-01-11 06:34:34’),
213. (48, 1, 64, ‘2016-01-11 06:48:54’),
214. (49, 1, 20, ‘2016-01-11 06:44:29’),
215. (50, 1, 80, ‘2016-01-11 06:48:55’),
216. (51, 1, 4, ‘2016-01-11 06:47:34’),
217. (52, 1, 96, ‘2016-01-11 06:40:26’),
218. (53, 1, 37, ‘2016-01-11 06:44:30’),
219. (54, 1, 88, ‘2016-01-11 06:34:51’),
220. (55, 1, 22, ‘2016-01-11 06:43:51’),
221. (56, 1, 83, ‘2016-01-11 06:45:06’),
222. (57, 1, 33, ‘2016-01-11 06:41:06’),
223. (58, 1, 70, ‘2016-01-11 06:48:13’),
224. (59, 1, 2, ‘2016-01-11 06:47:55’),
225. (60, 1, 77, ‘2016-01-11 06:47:47’),
226. (61, 1, 97, ‘2016-01-11 06:37:55’),
227. (62, 1, 8, ‘2016-01-11 06:36:25’),
228. (63, 1, 68, ‘2016-01-11 06:42:34’),
229. (64, 1, 90, ‘2016-01-11 06:41:42’),
230. (65, 1, 52, ‘2016-01-11 06:46:57’),
231. (66, 1, 41, ‘2016-01-11 06:38:32’),
232. (67, 1, 59, ‘2016-01-11 06:35:38’),
233. (68, 1, 1, ‘2016-01-11 06:45:05’),
234. (69, 1, 13, ‘2016-01-11 06:38:30’),
235. (70, 1, 78, ‘2016-01-11 06:35:16’),
236. (71, 1, 45, ‘2016-01-11 06:43:15’),
237. (72, 1, 82, ‘2016-01-11 06:34:31’),
238. (73, 1, 76, ‘2016-01-11 06:41:13’),
239. (74, 1, 15, ‘2016-01-11 06:49:01’),
240. (75, 1, 62, ‘2016-01-11 06:39:54’),
241. (76, 1, 23, ‘2016-01-11 06:44:52’),
242. (77, 1, 85, ‘2016-01-11 06:42:05’),
243. (78, 1, 47, ‘2016-01-11 06:43:52’),
244. (79, 1, 38, ‘2016-01-11 06:41:43’),
245. (80, 1, 93, ‘2016-01-11 06:36:03’),
246. (81, 1, 32, ‘2016-01-11 06:36:24’);
248. SELECT b.id,b.nama AS provinsi,
249. COUNT(*) AS jml,
250. COUNT(c.peserta_id) AS hadir,
251. CONCAT(TRUNCATE(COUNT(c.peserta_id)/COUNT(*)*100,0),‘%’) AS persen
252. FROM peserta a
253. RIGHT JOIN provinsi b ON b.id=a.provinsi
254. LEFT JOIN pertemuan_detail c ON c.peserta_id=a.id
255. GROUP BY b.id
257. +—-+—————————+—–+——-+——–+
258. | id | provinsi | jml | hadir | persen |
259. +—-+—————————+—–+——-+——–+
260. | 1 | Aceh | 3 | 3 | 100% |
261. | 2 | Sumatera Utara | 3 | 1 | 33% |
262. | 3 | Sumatera Barat | 1 | 0 | 0% |
263. | 4 | Riau | 1 | 0 | 0% |
264. | 5 | Jambi | 1 | 0 | 0% |
265. | 6 | Sumatera Selatan | 3 | 3 | 100% |
266. | 7 | Bengkulu | 5 | 3 | 60% |
267. | 8 | Lampung | 5 | 4 | 80% |
268. | 9 | Kepulauan Bangka Belitung | 5 | 5 | 100% |
269. | 10 | Kepulauan Riau | 3 | 2 | 66% |
270. | 11 | DKI Jakarta | 3 | 2 | 66% |
271. | 12 | Jawa Barat | 3 | 3 | 100% |
272. | 13 | Jawa Tengah | 3 | 3 | 100% |
273. | 14 | DI Yogyakarta | 4 | 4 | 100% |
274. | 15 | Jawa Timur | 7 | 7 | 100% |
275. | 16 | Banten | 3 | 0 | 0% |
276. | 17 | Bali | 5 | 4 | 80% |
277. | 18 | Nusa Tenggara Barat | 3 | 3 | 100% |
278. | 19 | Nusa Tenggara Timur | 1 | 1 | 100% |
279. | 20 | Kalimantan Barat | 3 | 2 | 66% |
280. | 21 | Kalimantan Tengah | 1 | 0 | 0% |
281. | 22 | Kalimantan Selatan | 2 | 2 | 100% |
282. | 23 | Kalimantan Timur | 1 | 1 | 100% |
283. | 24 | Kalimantan Utara | 4 | 4 | 100% |
284. | 25 | Sulawesi Utara | 2 | 1 | 50% |
285. | 26 | Sulawesi Tengah | 1 | 0 | 0% |
286. | 27 | Sulawesi Selatan | 2 | 2 | 100% |
287. | 28 | Sulawesi Tenggara | 2 | 2 | 100% |
288. | 29 | Gorontalo | 8 | 7 | 87% |
289. | 30 | Sulawesi Barat | 4 | 2 | 50% |
290. | 31 | Maluku | 3 | 3 | 100% |
291. | 32 | Maluku Utara | 3 | 3 | 100% |
292. | 33 | Papua Barat | 3 | 3 | 100% |
293. | 34 | Papua | 1 | 1 | 100% |
294. +—-+—————————+—–+——-+——–+
295. 34 rows in set (0.01 sec)

https://www.cahyadsn.com/detail_snippet.php?id=eG#