sum of a group by...

  • Thread starter Thread starter KenNiuM
  • Start date Start date
K

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..
 
hi Ken,
how do i actually do a sum of a group by?
Click to expand...
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.
Click to expand...
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.
Click to expand...
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 <--
 
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
 
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
 
hi,
Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?
Click to expand...
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 <--
 
sorry for the double post

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


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


Stefan Hoffmann said:
hi,
Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?
Click to expand...
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 <--
Click to expand...
 
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




Stefan Hoffmann said:
hi,
Thus i want to only get info from the time the cashier logs in to NOW().
How do i do that?
Click to expand...
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 <--
Click to expand...
 
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];
Click to expand...
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 <--
 
Back
Top