I have this query `

CREATE TABLE Commitment_fees
    (user_id INT PRIMARY KEY AUTO_INCREMENT,
    amount INT,
    success INT,
    date DATE);
    
ALTER TABLE Commitment_fees AUTO_INCREMENT=1000;
    
INSERT INTO Commitment_fees (amount, success, date)
VALUES
    (1000, 1, '2021-01-01'),
    (1010, 0, '2021-01-01'),
    (200, 1, '2021-01-01'),
    (201, 0, '2021-01-02'),
    (100, 0, '2021-01-02'),
    (101, 1, '2021-01-02');
    
SELECT
    date,
    SUM(amount) AS Attempt_amount,
    (SELECT
        SUM(amount)
    FROM
        Commitment_fees
    WHERE
        success = 1) AS Success_amount
FROM
    Commitment_fees

GROUP BY
    date;

` But my result is enter image description here You can see what a second day Success_amount is not correct

I try to `

SELECT
    date,
    SUM(amount) AS Attempt_amount,
    (SELECT
        SUM(amount)
    FROM
        Commitment_fees
    WHERE
        success = 1
    **GROUP BY
        date**) AS Success_amount
FROM
    Commitment_fees

GROUP BY
    date;

` But its return more than 1 row. What structure of this query i need?

CodePudding user response：

There is no need to use subquery,just using IF() can do it

 SELECT
    date,
    SUM(amount) AS Attempt_amount,
    sum(IF(success=1,amount,0)) AS Success_amount
FROM
    Commitment_fees
GROUP BY
    date;

DB Fiddle Demo

CodePudding user response：

CREATE TABLE Commitment_fees
    (user_id INT PRIMARY KEY AUTO_INCREMENT,
    amount INT,
    success INT,
    `date` DATE);
INSERT INTO Commitment_fees (amount, success, `date`)
VALUES
    (1000, 1, '2021-01-01'),
    (1010, 0, '2021-01-01'),
    (200, 1, '2021-01-01'),
    (201, 0, '2021-01-02'),
    (100, 0, '2021-01-02'),
    (101, 1, '2021-01-02');
SELECT * FROM Commitment_fees;

SELECT `date`,
       SUM(amount) AS Attempt_amount,
       SUM(CASE WHEN success THEN amount ELSE 0 END) AS Success_amount
FROM Commitment_fees
GROUP BY `date`;

fiddle