CODEWITHSUNDEEP
sqlms-access
ShaneL
ShaneL

Reputation: 87

Query - Trying to SUM one field based on content of another field

Table: 

DayOfWeek           Enrollments
Monday                 35
Monday                 12
Saturday               25
Tuesday                15
Monday                  9
Tuesday                15

Basically I'm trying to sum the total enrolments for each day.

so the Output will look like:

DayOfWeek           Enrollments
Monday                 56
Saturday               25
Tuesday                30

I've spent around 4 hours trying to work this out trying many many different ways but no luck.

The problem I'm having is i can count how many enrollments for each day but can't have it aligned with the correct day when i run the query e.g. I want The total to be on the same line as the day it was calculated from. (I hope that is clear enough)

Upvotes: 0

Views: 7631

Answers (1)

HansUp
HansUp

Reputation: 97101

Group by DayOfWeek, and ask for the sum of Enrollments within each group. The SQL will look like this.

SELECT DayOfWeek, Sum(Enrollments) AS SumOfEnrollments
FROM YourTable
GROUP BY DayOfWeek;

If you're using the Access query designer to create this, select your fields, then click the symbol for "Totals" query (Greek character sigma). In the "Total:" row of the design grid, select Group By and Sum for the appropriate fields.

Upvotes: 3

Related Questions