Real example for Total:Expression in query

%3CLINGO-SUB%20id%3D%22lingo-sub-2357105%22%20slang%3D%22en-US%22%3EReal%20example%20for%20Total%3AExpression%20in%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357105%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%20I%20would%20like%20to%20know%20how%20Total%3AExpression%20works%20in%20real%20life%20I%20can't%20find%20a%20real%20example.%20Can%20you%20give%20me%20a%20very%20simple%20example%20please%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JanZitniak_1-1621087476374.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280864iCA8D63C3F4D67C67%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JanZitniak_1-1621087476374.png%22%20alt%3D%22JanZitniak_1-1621087476374.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2357105%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357662%22%20slang%3D%22en-US%22%3ERe%3A%20Real%20example%20for%20Total%3AExpression%20in%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054603%22%20target%3D%22_blank%22%3E%40JanZitniak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20an%20aggregate%20(totals)%20query%2C%20you%20would%20use%20expression%20for%20any%20field%20you%20wish%20to%20display%20that%20is%20not%20being%20used%20for%20grouping%20and%20where%20options%20such%20as%20Sum%2FCount%2FMax%2Ffirst%20aren't%20appropriate.%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20this%20crosstab%20query%20based%20on%20aggregated%20fields%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ETRANSFORM%20First(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID)%20AS%20FirstOfCountOfAttMarkID%0ASELECT%20SchCalendar.DayDate%20AS%20%5BDate%5D%2C%20SchCalendar.SessionDay%20AS%20%5BDay%5D%2C%20SchCalendar.ValidAM%20AS%20%5BSchool%20Day%5D%2C%20SchCalendar.WeekNumber%2C%20SchCalendar.TermNumber%2C%20Sum(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID)%20AS%20%5BTotal%20Records%5D%0AFROM%20SchCalendar%20LEFT%20JOIN%20qryCountAttendanceMarksByDateSessionPeriod%20ON%20SchCalendar.DayDate%20%3D%20qryCountAttendanceMarksByDateSessionPeriod.Date%0AWHERE%20(((SchCalendar.SessionDay)%20Not%20Like%20'S*')%20AND%20((SchCalendar.WeekNumber)%20Is%20Not%20Null)%20AND%20((SchCalendar.TermNumber)%20Is%20Not%20Null))%0AGROUP%20BY%20SchCalendar.TermNumber%2C%20SchCalendar.DayDate%2C%20SchCalendar.SessionDay%2C%20SchCalendar.ValidAM%2C%20SchCalendar.WeekNumber%2C%20SchCalendar.TermNumber%2C%20SchCalendar.CycleDayNumber%0AORDER%20BY%20SchCalendar.TermNumber%2C%20SchCalendar.DayDate%0APIVOT%20qryCountAttendanceMarksByDateSessionPeriod.%5BAM%2FPM%5D%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESee%20attached%20screenshot%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357665%22%20slang%3D%22en-US%22%3ERe%3A%20Real%20example%20for%20Total%3AExpression%20in%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1007894%22%20target%3D%22_blank%22%3E%40isladogs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApologies.%20This%20is%20the%20screenshot%20of%20the%20query%20design%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Capture0.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280880i9091D02235989A56%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture0.PNG%22%20alt%3D%22Capture0.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi guys, I would like to know how Total:Expression works in real life I can't find a real example. Can you give me a very simple example please?

JanZitniak_1-1621087476374.png

 

5 Replies

@JanZitniak 

In an aggregate (totals) query, you would use expression for any field you wish to display that is not being used for grouping and where options such as Sum/Count/Max/first aren't appropriate.

For example, in this crosstab query based on aggregated fields:

TRANSFORM First(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID) AS FirstOfCountOfAttMarkID
SELECT SchCalendar.DayDate AS [Date], SchCalendar.SessionDay AS [Day], SchCalendar.ValidAM AS [School Day], SchCalendar.WeekNumber, SchCalendar.TermNumber, Sum(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID) AS [Total Records]
FROM SchCalendar LEFT JOIN qryCountAttendanceMarksByDateSessionPeriod ON SchCalendar.DayDate = qryCountAttendanceMarksByDateSessionPeriod.Date
WHERE (((SchCalendar.SessionDay) Not Like 'S*') AND ((SchCalendar.WeekNumber) Is Not Null) AND ((SchCalendar.TermNumber) Is Not Null))
GROUP BY SchCalendar.TermNumber, SchCalendar.DayDate, SchCalendar.SessionDay, SchCalendar.ValidAM, SchCalendar.WeekNumber, SchCalendar.TermNumber, SchCalendar.CycleDayNumber
ORDER BY SchCalendar.TermNumber, SchCalendar.DayDate
PIVOT qryCountAttendanceMarksByDateSessionPeriod.[AM/PM];

See attached screenshot:

 

@isladogs 

Apologies. This is the screenshot of the query design

 

Capture0.PNG

 

And finally, here's a better example. This time a standard (non-crosstab) aggregate query showing an expression on the field Demerits:Capture0.PNG

 

 

@isladogs thank you for your response. Now I understand. I've simplified the example. If we have a table of Employees like this:

 

JanZitniak_1-1621142084167.png

 

and we need e.g. to calculate SUM of turnover with VAT for every project I can use the following query (with Expression):

 

JanZitniak_2-1621142421798.png

 

That's it :).

 

Glad you have a working solution