How to make a correct subquery in mysql, if i need more than one row?

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?

>Solution :

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

Leave a Reply