CODEWITHSUNDEEP
mysqlsqlaggregate
Jinith
Jinith

Reputation: 438

MySQL Query for aggregate

I am stuck with a query for aggregation on a data set. My Data set is as :

enter image description here

Rel Scope is the scope of the release which is as Actual / Planned. I want the aggregate to be populated in "CatStat Scope" column which is the Scope of the category for a particular release.

Which might be like SUM(Actual) / SUM(Planned) where Category = 'xxx' and RelStat = 'yyy' where 'xxx' is the category on the same row and 'yyy' is the relstat on the same row.

I would expect a result like : enter image description here

The highlighted cells have the same figures as they belong to the same Category and RelStat.

Upvotes: 1

Views: 62

Answers (1)

Andomar
Andomar

Reputation: 238078

You could calculate the scope per (RelStat, Category) group in a subquery. Join back to the main table to be able to show individual rows:

select  yt.*
,       scope.CatStatScope
from    YourTabe yt
join    (
        select  RelStat
        ,       Category
        ,       sum(Actual) / sum(Planned) as CatStatScope
        from    YourTable
        group by
                RelStat
        ,       Category
        ) as scope
on      scope.RelStat = yt.RelStat
        and scope.Category = yt.Category

Upvotes: 1

Related Questions