CODEWITHSUNDEEP
mysqlsql
Rizky Muhammad
Rizky Muhammad

Reputation: 63

SUM after COUNT and Group By MYSQL

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

Upvotes: 0

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

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;

Upvotes: 2

Related Questions