MySQL grouping sum problem-CodePudding

The original data:
The

reference

changqing dongguan community Labour union has cancelled 11
Changqing dongguan community Labour union has to erase 1
Changqing dongguan community Labour union not to erase 1
Changqing transport committee has to erase 1
Changqing supply and marketing system committee has logged off 6
Changqing committee has supply and marketing system to erase 3
Changqing agriculture trade union has cancelled 2
Changqing NaZhiHui Town committee has to erase 1
Changqing south circular road community Labour union has cancelled 3
Committee on changqing health system hierarchy file 9
Changqing health systems committee has cancelled 3
Changqing health system committee has to erase 1
Changqing health systems committee not to erase 1
Changqing bureau of commerce committee has cancelled 9
Changqing bureau of commerce committee has been out of 5
Changqing bureau of commerce committee not to erase 1
Changqing town committee has cancelled 3
Changqing town committee has been out of 3
Changqing urban construction system committee has cancelled 1
Good changqing and vocational training centre union has cancelled 1
At changqing yao township committee has cancelled 1
At changqing yao township committee has to erase 1
Changqing bureau of ministry committee has cancelled 2
Changqing bureau of ministry committee has been out of 3
Changqing bureau of ministry committee not to erase 3
Changqing market supervision and administration committee has cancelled 3
Changqing market supervision and administration committee has to erase 1
Changqing education system committee level gear 1
Changqing education system committee has cancelled 7
Changqing committee education system has been out of 5
Changqing education system committee not to erase 2
Changqing culture system committee has cancelled 1
Service center of changqing hierarchy file 10
The town of changqing grahame committee has cancelled 1
Changqing HengShui Town committee has to erase 1
Changqing water conservancy system committee has cancelled 1
Changqing water conservancy system committee has to erase 2
The town of changqing Tian Jiazhuang committee has to erase 1
Changqing food system committee level file 5
Changqing food system committee has cancelled 1
Changqing food system committee has out of 12
Changqing food system committee not to erase 3
Changqing van home village township committee has to erase 1
The town of changqing GuoWang committee has to erase 1
Changqing Chen village committee has cancelled 1
Changqing food companies, trade unions have to erase 2
Changqing diet service company Labour union not to erase 5

Want to achieve:
Number of unit name, whether out of poverty, all kinds of poverty the total number of all kinds of poverty
Line tag hierarchy file did not save themselves have difficulty has logout
Changqing food system committee 5 3 12 1 and

Finally calculate the total number of all kinds of poverty? Group by, sum,

CodePudding user response:

If is a dynamic column, cannot do the like this..
Select
Unit name,
The sum (res) level) as the hierarchy,
The sum (res) not to erase) as not to erase,
The sum (res) have to erase) as,
The sum (res) have been cancelled) as has been cancelled,
The sum (res) in total) as a total
The from
(
Select
Unit name,
Case the when type='hierarchy file then sum (field) else 0 end as hierarchy,
Case the when type='not to erase then sum (field) else 0 end as not to erase,
Case when type='have to erase then sum (field) else 0 end as to erase,
Case the when type='cancelled' then sum (field) else 0 end as cancelled,
The sum (field) as a total
From the table name
Group by the name of the
) res
Group by the unit name