sum of a group by...

[KenNiuM]

how do i actually do a sum of a group by?


My SQL look something like this...

SELECT CashierLoginDetails.lStaffID, Last(CashierLoginDetails.[lDate/TIme])
AS [lDate/TImeOfLast], Last(CashierLoginDetails.lCashRegister) AS
lCashRegisterOfLast, SalesTransaction.stPaymentMode
FROM CashierLoginDetails, SalesTransaction
GROUP BY CashierLoginDetails.lStaffID, SalesTransaction.stPaymentMode;


I wan the last log in of the cashier. It would than show which cashRegister
i logged in at.
From the next table, i hope to capture the different payment mode, only
"NETS" and "CASH". I want to be able to sum up the total amount in the NETS
and CASH group.


This is how it looks like now..
http://i106.photobucket.com/albums/m268/KenNiuM/Untitled.jpg


The field with all the transaction amounts is stTotalAmount.
I want to sum up the amount in the table according to staffID, last time
till current, and payment mode.


Please help... i tried adding the field in the query and sum it, it doesn't
work..

 

[Stefan Hoffmann]

hi Ken,

how do i actually do a sum of a group by?

From the next table, i hope to capture the different payment mode, only
"NETS" and "CASH". I want to be able to sum up the total amount in the NETS
and CASH group.

The field with all the transaction amounts is stTotalAmount.
I want to sum up the amount in the table according to staffID, last time
till current, and payment mode.

What do you mean with "last time till current"?

SELECT staffID, stPaymentMode, SUM(stTotalAmount)
FROM SalesTransaction
WHERE <stTransactionDateTime> >= #Last Time#
GROUP BY staffID, stPaymentMode


mfG
--> stefan <--

 

[KenNiuM]

What do you mean with "last time till current"?

what i meant was stTransactionDateTime till NOW()

stTransactionDateTime default value is NOW().
Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?


I have tried the SQL you have provided, but the sum is not correct.
i have yet to input the time constrain

 

[KenNiuM]

What do you mean with "last time till current"?

what i meant was stTransactionDateTime till NOW()

stTransactionDateTime default value is NOW().
Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?


I have tried the SQL you have provided, but the sum is not correct.
i have yet to input the time constrain

 

[Stefan Hoffmann]

hi,

Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?

Add your query and join it via lStaffId. Then you can append your
date/time filter.

btw, rewrite your first query using Max() instead of Last() as Last()
returns the last physical record. So a Max([dateTime]) maybe not the
same as Last([dateTime]).


mfG
--> stefan <--

 

[KenNiuM]

sorry for the double post

SELECT staffID, stPaymentMode, SUM(stTotalAmount)
FROM SalesTransaction
WHERE <stTransactionDateTime> >= #Last Time#
GROUP BY staffID, stPaymentMode

how do you put lDate/TImeOfMax >= now()?

hi,

Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?

Add your query and join it via lStaffId. Then you can append your
date/time filter.

btw, rewrite your first query using Max() instead of Last() as Last()
returns the last physical record. So a Max([dateTime]) maybe not the
same as Last([dateTime]).


mfG
--> stefan <--

 

[KenNiuM]

http://s106.photobucket.com/albums/m268/KenNiuM/?action=view&current=Untitled1.jpg

This my my current SQL
SELECT Query1.lStaffID, SalesTransaction.stPaymentMode,
Sum(SalesTransaction.stTotalAmount) AS stTotalAmountOfSum,
Query1.[lDate/TImeOfMax]
FROM SalesTransaction, Query1
GROUP BY Query1.lStaffID, SalesTransaction.stPaymentMode,
Query1.[lDate/TImeOfMax];


As you can see from the picture, the sum is just the sum of "NETS' and "CASH".
How do i further constrain it?
by cashierID, lDate/TImeOfMax to NOW() and stPaymentMode

hi,

Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?

Add your query and join it via lStaffId. Then you can append your
date/time filter.

btw, rewrite your first query using Max() instead of Last() as Last()
returns the last physical record. So a Max([dateTime]) maybe not the
same as Last([dateTime]).


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

This my my current SQL
SELECT Query1.lStaffID, SalesTransaction.stPaymentMode,
Sum(SalesTransaction.stTotalAmount) AS stTotalAmountOfSum,
Query1.[lDate/TImeOfMax]
FROM SalesTransaction, Query1
GROUP BY Query1.lStaffID, SalesTransaction.stPaymentMode,
Query1.[lDate/TImeOfMax];

You need a INNER JOIN, e.g.:

SELECT
Q.lStaffID,
ST.stPaymentMode,
Sum(ST.stTotalAmount) AS stTotalAmountOfSum,
Q.[lDate/TImeOfMax]
FROM SalesTransaction ST
INNER JOIN Query1 Q
ON ST.lStaffID = Q.lStaffID
GROUP BY
Q.lStaffID,
ST.stPaymentMode,
Q.[lDate/TImeOfMax];

mfG
--> stefan <--