SUM after COUNT and Group By MYSQL

Question

I want to ask about SQL in mysql. Im stack over 1 hour :(
I have sql :

    SELECT TZL.IsMissed, COUNT(TZL.ChatID) as Amount FROM tblLog TZL group by TZL.IsMissed

And the result :

| IsMissed | Amount |
|        0 |    100 |
|        1 |    500 |

I want add one more column after Amount column, let say the name of new column is SumAmount.
i want SumAmount value is SUM of the Amount Column.

| IsMissed | Amount | SumAmount |
| 0 | 100 | 600 |
| 1 | 500 | 600 |

I already try sql like below :

SELECT
    tbl.*,SUM(tbl.Amount) as SumAmount
FROM
    (
        SELECT
            TZL.IsMissed,
            COUNT(TZL.ChatID) AS Amount
        FROM
            tblLog TZL
        GROUP BY
            TZL.IsMissed
    ) tbl
GROUP BY
tbl.IsMissed 
WITH ROLLUP

But with ROLLUP the result is add a new one row, not column. Anyone can teach me for this ? Thanks for answer

What's the point of adding a redundant column to the result set? — Karoly Horvath
– Karoly Horvath, Commented Sep 30, 2015 at 10:06
@KarolyHorvath : after i got the SumAmount i want to find the percentage value, and then i will add one more colum alias with value ((SumAmount/Amount)*100) — Rizky Muhammad
– Rizky Muhammad, Commented Sep 30, 2015 at 10:10
Sum of Amount when grouped by IsMissed in both queries is always same as Amount in inner query, what is the point? — Night2
– Night2, Commented Sep 30, 2015 at 10:10

Gordon Linoff · Accepted Answer · 2015-09-30 10:13:12Z

2

There are several ways to approach this. I would calculate the value in the from clause:

SELECT TZL.IsMissed, COUNT(TZL.ChatID) as Amount, tt.SumAmount
FROM tblLog TZL CROSS JOIN
     (SELECT COUNT(*) as SumAmount FROM tblLog) tt
GROUP BY TZL.IsMissed, tt.SumAmount;

answered Sep 30, 2015 at 10:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rizky Muhammad Over a year ago

thanks, its work. The key is use CROSS JOIN. ok, i must learn about cross join.

Collectives™ on Stack Overflow

SUM after COUNT and Group By MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related