CODEWITHSUNDEEP
mysql
Jeremy Myrtle
Jeremy Myrtle

Reputation: 89

MySQL COUNT not providing the correct results

I'm trying to create an attendance application and I'm having an issue with an SQL statement. I need it to count how many people showed up and how many were absent and get a total per precinct. Here is what I managed to come up with:

SELECT IFNULL(precinct, 'Totals') as precinct, COUNT(at.member_id) as delegates_present, COUNT(ab.member_id) as delegates_absent, COUNT(at.member_id) + COUNT(ab.member_id) as 'total' FROM attendance at LEFT JOIN members m ON at.member_id = m.id LEFT JOIN absence ab ON ab.member_id = m.id GROUP BY precinct WITH ROLLUP

but apparently, the absences column is not grabbing any data and it should have a 1 in the 10-00 row. What did I do incorrectly? I can't seem to figure it out.

Data set, query, result, and fiddle:

DROP TABLE IF EXISTS absence;

CREATE TABLE `absence` (
  `id` int(11) NOT NULL,
  `member_id` varchar(255) NOT NULL,
  `member_email` varchar(255) NOT NULL,
  `member_phone` varchar(12) NOT NULL,
  `absent` tinyint(4) NOT NULL,
  `absence_desc` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `absence` (`id`, `member_id`, `member_email`, `member_phone`, `absent`, `absence_desc`) VALUES
(1, '36227 ', '', '', 1, 'Out of town with family');

DROP TABLE IF EXISTS attendance;

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL,
  `member_id` varchar(255) NOT NULL,
  `member_email` varchar(255) DEFAULT NULL,
  `member_phone` varchar(12) DEFAULT NULL,
  `present` tinyint(4) NOT NULL,
  `alternate` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `attendance` (`id`, `member_id`, `member_email`, `member_phone`, `present`, `alternate`) VALUES
(1, '11698 ', '[email protected]', '704-788-9055', 1, NULL),
(2, '17698 ', '[email protected]', '704-425-8868', 1, 1),
(3, '36228 ', '[email protected]', '704-309-5076', 1, 1),
(4, '16921 ', '[email protected]', '704-791-2685', 1, NULL),
(5, '1437 ', '[email protected]', '704-791-7373', 1, 1),
(6, '16922 ', '[email protected]', '123-456-7890', 1, NULL);

DROP TABLE IF EXISTS members;

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `first_name` varchar(11) DEFAULT NULL,
  `middle_name` varchar(17) DEFAULT NULL,
  `suffix` varchar(3) DEFAULT NULL,
  `residential_address` varchar(48) DEFAULT NULL,
  `mailing_address` varchar(64) DEFAULT NULL,
  `precinct` varchar(5) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ethnicity` varchar(2) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `party` varchar(3) DEFAULT NULL,
  `race` varchar(1) DEFAULT NULL,
  `phone` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `members` (`id`, `last_name`, `first_name`, `middle_name`, `suffix`, `precinct`) VALUES
    (1437, 'CRUTCHFIELD', 'MATTHEW', 'WILLIAM', NULL, '01-04'),
    (11698, 'MYRTLE', 'JEREMY', 'ALLEN', NULL, '02-03'),
    (16921, 'CRUTCHFIELD', 'ANDREW', 'DAVID', NULL, '02-08'),
    (16922, 'CRUTCHFIELD', 'BRITTANY', 'RENEE', NULL, '02-08'),
    (17698, 'MOFFITT', 'PARISH', 'HENRY', NULL, '02-08'),
    (36227, 'CRUTCHFIELD', 'CHERYL', 'JERMYN', NULL, '10-00'),
    (36228, 'CRUTCHFIELD', 'KEVIN', 'ROGER', NULL, '10-00');

SELECT IFNULL(precinct, 'Totals') as precinct, 
COUNT(at.member_id) as delegates_present, 
COUNT(ab.member_id) as delegates_absent, 
COUNT(at.member_id) + COUNT(ab.member_id) as 'total' 
FROM attendance at 
LEFT JOIN members m ON at.member_id = m.id 
LEFT JOIN absence ab ON ab.member_id = m.id 
GROUP BY precinct WITH ROLLUP;

+----------+-------------------+------------------+-------+
| precinct | delegates_present | delegates_absent | total |
+----------+-------------------+------------------+-------+
| 01-04    |                 1 |                0 |     1 |
| 02-03    |                 1 |                0 |     1 |
| 02-08    |                 3 |                0 |     3 |
| 10-00    |                 1 |                0 |     1 |
| Totals   |                 6 |                0 |     6 |
+----------+-------------------+------------------+-------+
5 rows in set, 1 warning (0.08 sec)

SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1052 | Column 'precinct' in group statement is ambiguous |
+---------+------+---------------------------------------------------+

http://sqlfiddle.com/#!9/dc7e07/2/0

Upvotes: 2

Views: 46

Answers (2)

Cargo23
Cargo23

Reputation: 3189

Your problem here is that you are joining the attendance table and the absence table and then counting how many rows there are per precinct. You need to only count rows where the delegate was present.

My recommendation would be to eliminate the absence table and just use the attendence table to record both presence and absence. Then you can get your counts like this:

SELECT 
 ...
 SUM(CASE WHEN member_present=1 THEN 1 ELSE 0 END) as delegates_present,
 SUM(CASE WHEN member_present=0 THEN 1 ELSE 0 END) as delegates_absent,
 ...

Upvotes: 1

user10127657
user10127657

Reputation:

I think your joins are in the wrong order:

FROM members m
LEFT JOIN attendance at ON at.member_id = m.id 
LEFT JOIN absence ab ON ab.member_id = m.id

This should work. By starting your joins with attendance table, you never read the full member table.

Let me know if it works.

Upvotes: 4

Related Questions