SQL query assistance nested and count query

Question

I have 4 rows in a table: Players

PlayerName (8 distinct players)
Performance
   good
   bad
Salary (integer)
PositionPlayed (5 distinct positions)

I am looking to get an output where it shows the names of players and the count of number of times the player played in a particular position when performance is bad.

         James | Kobe | Jordan 
Center    5    |  8   |   5
PF        3    |  5   |   2
PG        2    |  1   |   0

I am new to SQL and I don't even know where to start for something like this. Please assist or provide some guidance on how to search on what I need to learn for this type of query

Thanks

Is the second table your desired output table? As this will require an additional transform on the PlayerName column (what are the 8 players names?) — QHarr, Oct 23 '17 at 04:16
This [answer](https://stackoverflow.com/questions/17470499/distinct-row-values-as-columns-sql-server) should fulfill your need. — Erfan Ahmed, Oct 23 '17 at 04:20
Re: ErfanAhmedEmon's shared link, OP needs to look at BlueFeets answer. Pivot on the PlayerName column. — QHarr, Oct 23 '17 at 04:25
To be exact this is actually similar to your question. Find the answer [here](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Erfan Ahmed, Oct 23 '17 at 04:30
I would encourage you to prepare the table with the axes swapped around. Use the fixed range of positions as column headings, and then Players are rows. SQL is much easier with fixed columns and variable rows. — Paul Maxwell, Oct 23 '17 at 04:54

score 0 · Accepted Answer · answered Oct 23 '17 at 05:04

IF you can live with "flipping" how that table is presented, this will enable production of that flipped layout:

SELECT
        PlayerName
      , COUNT(CASE WHEN PositionPlayed = 'Center' THEN PlayerName END) AS "Center"
      , COUNT(CASE WHEN PositionPlayed = 'PF'     THEN PlayerName END) AS "PF"
      , COUNT(CASE WHEN PositionPlayed = 'PG'     THEN PlayerName END) AS "PG"
FROM Players
WHERE Performance = 'bad'
GROUP BY PlayerName
ORDER BY PlayerName

It requires one row of that SQL code for each position, but you can report on as many players as you like without changing that query.

You can extend that logic, for example by having some columns for 'good' and others for 'bad' e.g.

SELECT
        PlayerName
      , COUNT(CASE WHEN Performance = 'good' and PositionPlayed = 'Center' THEN PlayerName END) AS "Center"
      , COUNT(CASE WHEN Performance = 'good' and PositionPlayed = 'PF'     THEN PlayerName END) AS "PF"
      , COUNT(CASE WHEN Performance = 'good' and PositionPlayed = 'PG'     THEN PlayerName END) AS "PG"

      , COUNT(CASE WHEN Performance = 'bad'  and PositionPlayed = 'Center' THEN PlayerName END) AS "Center"
      , COUNT(CASE WHEN Performance = 'bad'  and PositionPlayed = 'PF'     THEN PlayerName END) AS "PF"
      , COUNT(CASE WHEN Performance = 'bad'  and PositionPlayed = 'PG'     THEN PlayerName END) AS "PG"
FROM Players
GROUP BY PlayerName
ORDER BY PlayerName
;

score 0 · Answer 2 · answered Oct 23 '17 at 05:35

Assuming you table structure be like this -

Create table player(
 id int(11) not null auto_increment,
 player_name varchar(20) not null,
 performance varchar(4) not null,
 position_played varchar(2) not null,
 constraint primary key (id)
);

And have some sample data like -

insert into player(player_name, performance, position_played)
values('a', 'good', 'p1'),
      ('a', 'bad', 'p3'),
      ('a', 'bad', 'p3'),
      ('b', 'good', 'p2'),
      ('b', 'bad', 'p2'),
      ('b', 'good', 'p1'),
      ('c', 'bad', 'p1'),
      ('c', 'bad', 'p2'),
      ('c', 'good', 'p3');

Your desired output will be generate by running this query-

SELECT  P.`position_played`,
COUNT(
    CASE 
        WHEN P.`performance`='bad' and p.`player_name`='a'
        THEN p.player_name
        ELSE NULL 
    END
) AS 'Player A',
COUNT(
    CASE 
        WHEN P.`performance`='bad' and p.`player_name`='b'
        THEN p.player_name
        ELSE NULL 
    END
) AS 'Player B',
COUNT(
    CASE 
        WHEN P.`performance`='bad' and p.`player_name`='c'
        THEN p.player_name
        ELSE NULL 
    END
) AS 'Player C'
FROM    player P
GROUP BY P.`position_played`;

Find output result here

And for every new player you would add another column,and another, and another until you get dizzy or MySQL gives up. — Paul Maxwell, Oct 23 '17 at 05:52

SQL query assistance nested and count query

2 Answers2