MySQL Correct Query to Obtain Sum and Count of Columns

Viewed 35

I have the following query.

SELECT
    b.id, b.prid, b.title,
    p.fullname,
    m.enroll, m.point,
    SUM(m2.enroll) enrolls, COUNT(m2.point) points,
    COUNT(DISTINCT r.id) reviewal
FROM books b
    JOIN person p ON p.id = b.prid
    LEFT JOIN promotes m ON m.bkid = b.id AND m.prid = p.id
    LEFT JOIN promotes m2 ON m2.bkid = b.id
    LEFT JOIN reviews r ON r.bkid = b.id
GROUP BY b.id 
ORDER BY b.id ASC;

**Output**

| id  | prid | title     | fullname | enroll | point | enrolls | points | reviewal |
| --- | ---- | --------- | -------- | ------ | ----- | ------- | ------ | -------- |
| 1   | 1    | Physics   | Jade     | 1      | 2     | 1       | 1      | 0        |
| 2   | 2    | Chemistry | Jack     | 1      | 1     | 4       | 4      | 2        |
| 3   | 3    | Maths     | John     | 1      |       | 1       | 1      | 1        |

However, the query is returning wrong results for the enrolls and points columns. The correct results for the two columns are:

| id  | enrolls | points |
| --- | ------- | ------ |
| 1   | 1       | 1      |
| 2   | 2       | 2      |
| 3   | 1       | 1      |

What should be the correct query?

Table structure and the data (DB FIDDLE):

CREATE TABLE `books` (
      `id` int(10) UNSIGNED NOT NULL,
      `prid` int(10) UNSIGNED NOT NULL,
      `title` varchar(100) NOT NULL
    );
    
    INSERT INTO `books` (`id`, `prid`, `title`) VALUES
    (1, 1, 'Physics'), (2, 2, 'Chemistry'), (3, 3, 'Maths');
    
    CREATE TABLE `person` (
      `id` int(10) UNSIGNED NOT NULL,
      `fullname` varchar(100) NOT NULL
    );
    
    INSERT INTO `person` (`id`, `fullname`) VALUES
    (1, 'Jade'), (2, 'Jack'), (3, 'John');
    
    CREATE TABLE `promotes` (
      `bkid` int(11) UNSIGNED NOT NULL,
      `prid` int(11) UNSIGNED NOT NULL,
      `enroll` tinyint(1) DEFAULT 1,
      `point` tinyint(1) DEFAULT NULL
    );
    
    INSERT INTO `promotes` (`bkid`, `prid`, `enroll`, `point`) VALUES
    (1, 1, 1, 2), (2, 1, 1, 3), (2, 2, 1, 1),
    (3, 1, NULL, 0), (3, 2, 0, NULL), (3, 3, 1, NULL);
    
    CREATE TABLE `reviews` (
      `id` int(10) NOT NULL,
      `prid` int(10) UNSIGNED NOT NULL,
      `bkid` int(10) UNSIGNED NOT NULL,
      `review` varchar(500) NOT NULL
    );
    
    INSERT INTO `reviews` (`id`, `prid`, `bkid`, `review`) VALUES
    (1, 1, 2, 'Nice'), (2, 1, 3, 'Thinking'), (3, 2, 2, 'Try it');
    
    ALTER TABLE `books` ADD PRIMARY KEY (`id`),
    ADD KEY `book_prid_fk` (`prid`);
    
    ALTER TABLE `person` ADD PRIMARY KEY (`id`);
    
    ALTER TABLE `promotes` ADD PRIMARY KEY (`bkid`,`prid`),
    ADD KEY `promotes_prid_fk` (`prid`);
    
    ALTER TABLE `reviews` ADD PRIMARY KEY (`id`),
    ADD KEY `reviews_prid_fk` (`prid`), ADD KEY `reviews_bkid_fk` (`bkid`);
    
    ALTER TABLE `books`
      MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
      
    ALTER TABLE `person`
      MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;  
      
    ALTER TABLE `reviews`
      MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
      
    ALTER TABLE `books`
      ADD CONSTRAINT `book_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
      
    ALTER TABLE `promotes`
      ADD CONSTRAINT `promotes_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
      ADD CONSTRAINT `promotes_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
      
    ALTER TABLE `reviews`
      ADD CONSTRAINT `reviews_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
      ADD CONSTRAINT `reviews_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);

The enroll column in the promotes table may contain a value from null, 0, or 1. Similarly, the point column value could be either null or 1-5.

1 Answers

Try doing the aggregation on a subquery, because the aggregation comes after the JOIN condition , that's why your values multiplies.

Try:

SELECT b.id, 
       b.prid, 
       b.title,
       p.fullname,
       m.enroll, 
       m.point,
       m2.enrolls, 
       m2.points,
       COUNT(DISTINCT r.id) reviewal
FROM books b
JOIN person p ON p.id = b.prid
LEFT JOIN promotes m ON m.bkid = b.id AND m.prid = p.id
LEFT JOIN  (select bkid,
                   SUM(enroll) enrolls,
                   COUNT(point) points
            from promotes
            group by bkid 
           ) as m2 ON m2.bkid = b.id
LEFT JOIN reviews r ON r.bkid = b.id
GROUP BY b.id 
ORDER BY b.id ASC

https://dbfiddle.uk/Q-cPIMoD

Related