How to do Sum of Sums in Query

[braveali]

Hello All,

I have a table with CityID and Schools Register with City i.e.

CityID	SchoolRegister
Hyd	5
Karachi	10
Hyd	20
Karachi	10

I have put a query in this table:Enrollment

Field: CityID SchoolRegister
Table: Enrollment Enrollment
Total: Group By Sum

The result of it is:

CityID SchoolRegister
Hyd 25
Karachi 20


I would like to have a Third column what will automatically do sum of School Register which is 45,

In the Third Column of Query, I click on Expression Builder and typed TotalSum: [schoolregister] but it is summing up
all Hyd and Karachi Schools not summing the sum of total schools.


I will appreciate your help please.

Thanks

Many regards

[lfpm062010]

In design mode, change the TotalSum to TotalSum: DSum("SchoolRegister","Enrollment")

[braveali]

Hi,

Thanks for your reply, Yes its summing up all the Schools Register in Total Sum Column, but the sum is appearing in each row: i.e.

CityID	SchoolRegister	TotalSum
Hyd	25	45
Karachi	20	45

I would like to have only one sum in TotalSum Column if possible.


Thanks


Many regards

[lfpm062010]

Is this the output you are looking for?


CityID SchoolRegister
Hyd 25
Karachi 20
TotalSum 45

If that is the case, you have to use the UNION to create the "TotalSum" Record.

SELECT Enrollment.CityID, Sum(Enrollment.SchoolRegister) AS SumOfSchoolRegister
FROM Enrollment
GROUP BY Enrollment.CityID
UNION
SELECT "TotalSum", Sum(Enrollment.SchoolRegister) AS SumOfSchoolRegister
FROM Enrollment;

[braveali]

Thanks a million... Its solved with your guidance