Hi i have a table like so it includes a user id, date, amount and active flag

my requirement is to get the total amount for this particular user for the whole day that he was active, so since the user was active on the date of '2017-07-12' i should be able to get all the amount for that particular date so my amount for this particular user would be 27.

What would be a right query to perform this action in mysql by looking at the active flag and how would i go about to do it?

CodePudding user response：

We can use an aggregation approach here:

SELECT id, SUM(amount) AS total_amount
FROM
(
    SELECT *
    FROM yourTable t1
    WHERE active = 1 OR
          EXISTS (SELECT 1
                  FROM yourTable t2
                  WHERE t2.date = t1.date AND
                        t2.active = 1)
) t
GROUP BY id
ORDER BY id;

Demo

CodePudding user response：

Either

SELECT id, `date`, SUM(amount) amount
FROM table
GROUP BY 1, 2
HAVING SUM(active)

or

SELECT id, `date`, SUM(amount) * (SUM(active) > 0) amount
FROM table
GROUP BY 1, 2

depends on desired output (does "non-active" dates must be skipped at all, or they'd be returned with zero amount).

CodePudding user response：

Another solution:

select tbl.id,
       tbl.`date`,
       sum(amount) as tot_date_amount
from tbl
inner join (select `date`
            from tbl 
            where active = 1
            ) as t2 on tbl.`date`=t2.`date`
group by id,`date`;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c173dcc9a72490146ff7c094a10b08b6

The subquery will select only the dates where active = 1 .Using inner join will return only the sum for the active = 1 dates