How to show count of records

Question

I have the following data

   ReasonId   Team   Division Location
     2          A                 L1
     3          B        D1       L2
     2          A        D2       L1 
     2          A        D3       L3

I want to show the count grouped by the ReasonId for each team,division & location. There could be instances where division could be null.

I am trying something like this,

  SELECT 
    COUNT(*) AS TotalRequests, Reason, team
FROM
    reports
GROUP BY Reason , team
UNION SELECT 
    COUNT(*) AS TotalRequests, Reason, location
FROM
    reports
GROUP BY Reason , location 
UNION SELECT 
    COUNT(*) AS TotalRequests, Reason, division
FROM
    reports
WHERE
    ISNULL(division) = 0
GROUP BY Reason , division
;

The output I am getting for the above is,

   TotalRequests    Reason  team
        1             2 
        3             2      A
        1             3      B
        1             3     D1
        1             2     D2
        1             2     D3
        2             2     L1
        1             3     L2
        1             2     L3

Is it possible to get an output that looks like this,

ReasonId   Team  TotalByTeam   Location TotalByLocation  Division TotalByDivision
  2         A       3             L1          2                          0
  2         A       3             L3          1              D2          1
  2         A       3             L3          1              D3          1
  3         B       1             L2          1              D1          1

I am using mysql 8.0.17 Here's a sample schema and dbfiddle of same

CREATE TABLE `reports` (
  `Reason` int(11) DEFAULT NULL,
  `Team` text,
  `Division` text,
  `Location` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A',null,'L1');
    INSERT INTO reports (Reason,Team,Division,Location) values (3,  'A','D1','L2');
    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A','D2','L1');
    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A','D3','L3');

user_2270762 · Answer

You should use analytic functions COUNT(...) OVER (...) for this. They are available in MySQL since version 8.0.

select
  reasonid,
  team,
  count(team) over (partition by team) as total_by_team,
  location,
  count(location) over (partition by location) as total_by_location,
  division,
  count(division) over (partition by division) as total_by_division
from reports;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=79891554331e8222041ec34eea3fc4ee

user_3652345 · Answer

Try this below script-

Demo Here

SELECT A.ReasonId,
A.Team,
(SELECT COUNT(*) FROM your_table B WHERE B.ReasonId = A.ReasonId AND B.Team = A.Team) TotalByTeam,
A.Division,
(SELECT COUNT(*) FROM your_table B WHERE B.ReasonId = A.ReasonId AND B.Division = A.Division) TotalByDivision,
A.Location,
(SELECT COUNT(*) FROM your_table B WHERE B.ReasonId = A.ReasonId AND B.Location = A.Location) TotalByLocation
FROM your_table A

How to show count of records

2 Answers