StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Query on Group by issues Please help me..?
text
Body
copied!<p>My tables are:</p> <pre><code>CREATE TABLE IF NOT EXISTS `maf_game` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `maf_game` (`id`, `name`) VALUES (1, 'Finders Keepers'), (2, 'My american farm'); CREATE TABLE IF NOT EXISTS `maf_game_stats` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(100) NOT NULL, `sessionid` varchar(100) NOT NULL, `gid` int(11) NOT NULL, `qid` int(11) NOT NULL, `result` varchar(50) NOT NULL, `accesstime` datetime NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `maf_game_stats` (`id`, `userid`, `sessionid`, `gid`, `qid`, `result`, `accesstime`) VALUES (1, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 1, '1', '2013-03-22 18:26:41'), (2, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 2, '1', '2013-03-22 18:26:43'), (3, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 1, '1', '2013-03-22 18:27:15'), (4, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 2, '1', '2013-03-22 18:27:17'), (5, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 3, '0', '2013-03-22 18:27:21'), (6, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 4, '1', '2013-03-22 18:27:23'), (7, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 5, '1', '2013-03-22 18:28:28'), (8, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 1, '1', '2013-03-22 18:29:01'), (9, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 2, '1', '2013-03-22 18:29:02'), (10, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 3, '1', '2013-03-22 18:29:07'), (11, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 4, '0', '2013-03-22 18:29:09'), (12, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 5, '1', '2013-03-22 18:29:10'), (13, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 1, '0', '2013-03-22 18:42:22'), (14, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 2, '0', '2013-03-22 18:42:24'), (15, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 3, '0', '2013-03-22 18:42:26'), (16, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 4, '0', '2013-03-22 18:42:27'), (17, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 5, '0', '2013-03-22 18:42:29'), (18, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 1, '1', '2013-03-22 18:55:07'), (19, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 2, '1', '2013-03-22 18:55:08'), (20, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 3, '1', '2013-03-22 18:55:14'), (21, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 4, '1', '2013-03-22 18:55:15'), (22, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 1, '1', '2013-03-22 18:58:50'), (23, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 2, '1', '2013-03-22 18:58:52'); </code></pre> <p>My Written Query is :</p> <pre><code>SELECT D.gid, D.qid, D.result, COUNT(D.id) AS staus FROM (SELECT MIN(id) AS id, userid, gid, qid, result, accesstime FROM maf_game_stats WHERE gid=2 GROUP BY userid, qid) AS D GROUP BY D.qid, D.result </code></pre> <p>Above Query result:</p> <pre><code>gid qid result staus 2 1 0 1 2 1 1 2 2 2 0 1 2 2 1 2 2 3 0 1 2 3 1 2 2 4 0 2 2 4 1 1 2 5 0 1 2 5 1 1 </code></pre> <p>its working fine but I Want result is like Ex:</p> <ol> <li>How many students got each question Correct / Incorrect on their first attempt?</li> </ol> <p>result</p> <pre><code>-------------------------------------------- gid qid Correct Incorrect ------------------------------------------- 1 1 3 10 1 2 8 1 1 3 2 5 1 4 1 7 1 5 4 3 </code></pre> <p>Please any suggestions....?</p> <p>Thanks! Vasanth <br/> 22MR2013 <br/></p> <p>Sorry i Forgot..... who took first attempt</p> <ol> <li>How many students got each question Correct / Incorrect on their first attempt?</li> </ol> <pre> CREATE TABLE IF NOT EXISTS `maf_game` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ; INSERT INTO `maf_game` (`id`, `name`) VALUES (1, 'Finders Keepers'), (2, 'My american farm'), (3, 'Stewardship'), (4, 'Harvet This'); CREATE TABLE IF NOT EXISTS `maf_game_stats` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(100) NOT NULL, `sessionid` varchar(100) NOT NULL, `gid` int(11) NOT NULL, `qid` int(11) NOT NULL, `result` varchar(50) NOT NULL, `accesstime` datetime NOT NULL, PRIMARY KEY (`id`) ) ; INSERT INTO `maf_game_stats` (`id`, `userid`, `sessionid`, `gid`, `qid`, `result`, `accesstime`) VALUES (1, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 1, '1', '2013-03-22 18:26:41'), (2, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 2, '1', '2013-03-22 18:26:43'), (3, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 1, '1', '2013-03-22 18:27:15'), (4, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 2, '1', '2013-03-22 18:27:17'), (5, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 3, '0', '2013-03-22 18:27:21'), (6, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 4, '1', '2013-03-22 18:27:23'), (7, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 5, '1', '2013-03-22 18:28:28'), (8, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 1, '1', '2013-03-22 18:29:01'), (9, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 2, '1', '2013-03-22 18:29:02'), (10, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 3, '1', '2013-03-22 18:29:07'), (11, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 4, '0', '2013-03-22 18:29:09'), (12, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 5, '1', '2013-03-22 18:29:10'), (13, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 1, '0', '2013-03-22 18:42:22'), (14, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 2, '0', '2013-03-22 18:42:24'), (15, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 3, '0', '2013-03-22 18:42:26'), (16, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 4, '0', '2013-03-22 18:42:27'), (17, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 5, '0', '2013-03-22 18:42:29'), (18, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 1, '1', '2013-03-22 18:55:07'), (19, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 2, '1', '2013-03-22 18:55:08'), (20, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 3, '1', '2013-03-22 18:55:14'), (21, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 4, '1', '2013-03-22 18:55:15'), (22, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 1, '1', '2013-03-22 18:58:50'), (23, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 2, '1', '2013-03-22 18:58:52'), (24, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 1, '1', '2013-03-22 20:06:29'), (25, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 2, '1', '2013-03-22 20:06:31'), (26, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 3, '1', '2013-03-22 20:06:34'), (27, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 1, '1', '2013-03-22 20:06:41'), (28, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 2, '1', '2013-03-22 20:06:43'), (29, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 3, '1', '2013-03-22 20:06:45'), (30, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 4, '1', '2013-03-22 20:06:47'), (31, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 5, '1', '2013-03-22 20:06:49'), (32, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 1, '0', '2013-03-22 20:06:55'), (33, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 2, '1', '2013-03-22 20:06:56'), (34, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 3, '0', '2013-03-22 20:06:58'), (35, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 4, '0', '2013-03-22 20:06:59'), (36, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 5, '1', '2013-03-22 20:07:00'), (37, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 1, '0', '2013-03-23 10:26:56'), (38, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 2, '1', '2013-03-23 10:26:58'), (39, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 3, '0', '2013-03-23 10:26:59'), (40, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 4, '0', '2013-03-23 10:27:00'), (41, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 5, '1', '2013-03-23 10:27:01'), (42, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 1, '1', '2013-03-23 10:27:04'), (43, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 2, '1', '2013-03-23 10:27:06'), (44, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 3, '1', '2013-03-23 10:27:09'), (45, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 4, '1', '2013-03-23 10:27:10'), (46, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 5, '1', '2013-03-23 10:27:11'), (47, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 1, '1', '2013-03-23 10:27:14'), (48, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 2, '1', '2013-03-23 10:27:15'), (49, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 3, '1', '2013-03-23 10:27:18'), (50, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 4, '1', '2013-03-23 10:27:19'), (51, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 5, '1', '2013-03-23 10:27:20'), (52, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 1, '1', '2013-03-23 10:27:40'), (53, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 2, '1', '2013-03-23 10:27:41'), (54, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 3, '1', '2013-03-23 10:27:43'), (55, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 4, '1', '2013-03-23 10:27:44'), (56, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 5, '1', '2013-03-23 10:27:45'), (57, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 1, '1', '2013-03-23 11:04:50'), (58, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 2, '1', '2013-03-23 11:05:04'), (59, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 3, '1', '2013-03-23 11:05:06'), (60, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 4, '0', '2013-03-23 11:05:06'), (61, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 5, '0', '2013-03-23 11:05:07'), (62, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 1, '0', '2013-03-23 11:05:11'), (63, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 2, '1', '2013-03-23 11:05:12'), (64, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 3, '1', '2013-03-23 11:05:15'), (65, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 4, '1', '2013-03-23 11:05:19'), (66, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 5, '1', '2013-03-23 11:05:20'); </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload