Question 22110 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0 
    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;
    user_id amount success date
    1 1000 1 2021-01-01
    2 1010 0 2021-01-01
    3 200 1 2021-01-01
    4 201 0 2021-01-02
    5 100 0 2021-01-02
    6 101 1 2021-01-02 
    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`;
    date Attempt_amount Success_amount
    2021-01-01 2210 1200
    2021-01-02 402 101

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search