how do i rewrite the correct statement for COUNT under this condition

Bring back the count of all the batsmen who have played a certain number of innings only if there are more than one batsman who have played that number of innings. 4 innings – 2 batsmen, 5 innings – 3 batsmen etc.

SELECT COUNT(Innings) AS TotalInnings, COUNT(player) AS Totalplayer 
FROM Batting
GROUP BY Player

Player is the field name for the batsmens name. I am kinda stuck on how to write the last lines statement

4 innings – 2 batsmen, 5 innings – 3 batsmen etc

This is the table design

CREATE TABLE Batting (
            Player CHAR(25),
            CarrerSpan VARCHAR(20),
            Matches INT,
            Innings INT,
            Playing INT,
            Runs INT,
            HighestScore CHAR(10),
            AverageScore NUMERIC,
            BallsFaced INT,
            StrikeRate NUMERIC,
            Hundreds INT,
            Fifties INT,
            Zeros INT,
            Fours INT,
            Sixs INT
);


INSERT INTO Batting
VALUES ('JP Duminy', '2007-2018',   77, 71, 22, 1825,   '96*',  37.24,  1468,   124.31, 0,  11, 6,  130,    65);
INSERT INTO Batting
VALUES ('AB de Villiers', '2006-2017',  78, 75, 11, 1672,   '79*',  26.12,  1237,   135.16, 0,  10, 5,  140,    60);
INSERT INTO Batting
VALUES ('HM Amla', '2009-2018', 41, 41, 5,  1158,   '97*',  32.16,  883,    131.14, 0,  7,  2,  133,    23);
INSERT INTO Batting
VALUES ('F du Plessis', '2012-2017',    36, 36, 6,  1129,   119,    37.63,  849,    132.97, 1,  7,  0,  102,    35);
INSERT INTO Batting
VALUES('DA Miller', '2010-2018',    58, 51, 15, 1043,   '101*', 28.97,  745,    140.00, 1,  1,  0,  71, 46);
INSERT INTO Batting
VALUES('GC Smith', '2005-2011', 33, 33, 2,  982,    '89*',  31.67,  770,    127.53, 0,  5,  1,  123,    26);
INSERT INTO Batting
VALUES('Q de Kock','2012-2018', 32, 32, 4,  821,    59, 29.32,  637,    128.88, 0,  2,  3,  97, 24);
INSERT INTO Batting
VALUES('JH Kallis', '2005-2012',    25, 23, 4,  666,    73, 35.05,  558,    119.35, 0,  5,  0,  56, 20);
INSERT INTO Batting
VALUES('JA Morkel', '2005-2015',    50, 38, 11, 572,    43, 21.18,  402,    142.28, 0,  0,  1,  29, 39);
INSERT INTO Batting
VALUES('F Behardien',   '2012-2018',    37, 29, 13, 515,    '64*',  32.18,  402,    128.10, 0,  1,  1,  37, 16);
INSERT INTO Batting
VALUES('HH Gibbs',  '2005-2010',    23, 23, 1,  400,    '90*',  18.18,  318,    125.78, 0,  3,  4,  45, 12);
INSERT INTO Batting
VALUES('RR Rossouw',    '2014-2016',    15, 14, 3,  327,    78, 29.72,  237,    137.97, 0,  2,  2,  29, 12);
INSERT INTO Batting
VALUES('LE Bosman', '2006-2010',    14, 14, 1,  323,    94, 24.84,  219,    147.48, 0,  3,  2,  27, 20);
INSERT INTO Batting
VALUES('RR Hendricks',  '2014-2018',    13, 13, 0,  294,    70, 22.61,  262,    112.21, 0,  1,  2,  33, 3);
INSERT INTO Batting
VALUES('MV Boucher',    '2005-2010',    25, 21, 6,  268,    '36*',  17.86,  275,    97.45,  0,  0,  0,  22, 2);
INSERT INTO Batting
VALUES('RE Levi',   '2012-2012',    13 ,13, 2,  236,    '117*', 21.45,  167,    141.31, 1,  1,  3,  20, 15);
INSERT INTO Batting
VALUES('MN van Wyk',    '2007-2015',    8, 7,   1,  225,    '114*', 37.50,  157,    143.31, 1,  1,  0,  19, 14);
INSERT INTO Batting
VALUES('CA Ingram', '2010-2012',    9, 9,   1,  210,    78, 26.25,  162,    129.62, 0,  1,  1,  23, 7);
INSERT INTO Batting
VALUES('JM Kemp',   '2005-2007',    8, 7,   3,  203,    '89*',  50.75,  160,    126.87, 0,  1,  0,  17, 10);
INSERT INTO Batting
VALUES('J Botha',   '2006-2012',    40, 0,  9,  201,    34, 18.27,  165,    121.81, 0,  0,  1,  15, 9);
INSERT INTO Batting
VALUES('H Davids',  '2012-2013',    9, 9,   0,  161,    68, 17.88,  134,    120.14, 0,  2,  2,  18, 4);
INSERT INTO Batting
VALUES('JL Ontong', '2008-2015',    14, 10, 0,  158,    48, 15.80,  109,    144.95, 0,  0,  1,  6,  11);
INSERT INTO Batting
VALUES('JT Smuts',  '2017-2018',    8, 8,   0,  126,    45, 15.75,  114,    110.52, 0,  0,  1,  14, 5);
INSERT INTO Batting
VALUES('RJ Peterson',   '2006-2014',    21, 12, 4,  124,    34, 15.50,  113,    109.73, 0,  0,  1,  13, 2);
INSERT INTO Batting
VALUES('WD Parnell',    '2009-2017',    40, 13, 9,  114,    '29*',  28.50,  96, 118.75, 0,  0,  0,  10, 3);
INSERT INTO Batting
VALUES( 'H Klaasen', '2018-2018', 4, 4, 0, 110, '69', 27.50, 64, 171.87, 0, 1, 0, 5, 9);
INSERT INTO Batting
VALUES( 'M Mosehle', '2017-2017', 7, 6, 1, 105, '36', 21.00, 65, 161.53, 0, 0, 0, 6, 9);
INSERT INTO Batting
VALUES( 'D Wiese', '2013-2016', 20, 11, 4, 92, '28', 13.14, 75, 122.66, 0, 0, 1, 4, 3);
INSERT INTO Batting
VALUES( 'SM Pollock', '2005-2008', 12, 9, 2, 86, '36*', 12.28, 70, 122.85, 0, 0, 3, 4, 4);
INSERT INTO Batting
VALUES( 'CH Morris', '2012-2018', 17, 10, 3, 77, '17*', 11.00, 70, 110.00, 0, 0, 2, 7, 2);
INSERT INTO Batting
VALUES( 'RE van der Merwe', '2009-2010', 13, 6, 3, 57, '48', 19.00, 50, 114.00, 0, 0, 1, 2, 4);
INSERT INTO Batting
VALUES( 'C Jonker', '2018-2018', 1, 1, 0, 49, '49', 49.00, 24, 204.16, 0, 0, 0, 5, 2);
INSERT INTO Batting
VALUES( 'HG Kuhn', '2009-2017', 7, 6, 2, 49, '29', 12.25, 42, 116.66, 0, 0, 0, 3, 2);
INSERT INTO Batting
VALUES( 'JJ van der Wath', '2006-2007', 8, 4, 1, 46, '21', 15.33, 39, 117.94, 0, 0, 0, 3, 1);
INSERT INTO Batting

Answer

I think this could be the solution to your problem:

Query1

SELECT    Innings, 
          COUNT(player) AS Totalplayer 
FROM      Batting
GROUP BY  Innings
HAVING    COUNT(player) > 1;

Query2

SELECT    Innings, 
          COUNT(player) AS Totalplayer 
FROM      Batting
GROUP BY  Innings
HAVING    COUNT(Innings) > 1;

Sample Output :

Innings	Totalplayer
4	2
6	3
7	2
9	3
10	2
13	3
14	2
23	2

View on DB Fiddle

Advertisement

Answer