MySQL Correct Query to Obtain Sum and Count of Columns

Question

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.

Please consider of using window-functions https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html — Kaiser, Sep 20 '22 at 19:56
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Sep 20 '22 at 22:22
Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Sep 20 '22 at 22:44

score 1 · Answer 1 · answered Sep 20 '22 at 19:48

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

MySQL Correct Query to Obtain Sum and Count of Columns

1 Answers1