ADDING WITH COUNTING

Below is my query so far. There is a field in Winpayment call
transaction_amount that I would like to add up for use count statement if
possible. So what I am asking is there a way to for each count case add the
transaction amount and have a transaction total show for each count case
statement. For example, every exsistence in the first count statement would
be added up by using the transaction_amount and then have a field right afte
r
SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
Use Winpayment
GO
SELECT S.card_acceptor_identification STORE,
COUNT(M.card_acceptor_identification) TOTAL,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
FROM Store S
Left Join financial_message M
On M.card_acceptor_identification = S.card_acceptor_identification
And settlement_batch_number = '961'
AND id_number_1 like '540168%'
Where len (S.card_acceptor_identification) = 4
GROUP BY S.card_acceptor_identificationI'm not following what you are trying to do. In addition to your query,
could you post the DDL and a few rows of sample data and something showing
your expected result?
--Brian
(Please reply to the newsgroups only.)
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:8C71D6DF-414C-4B77-95CD-2934AB2CA45D@.microsoft.com...
> Below is my query so far. There is a field in Winpayment call
> transaction_amount that I would like to add up for use count statement if
> possible. So what I am asking is there a way to for each count case add
> the
> transaction amount and have a transaction total show for each count case
> statement. For example, every exsistence in the first count statement
> would
> be added up by using the transaction_amount and then have a field right
> after
> SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
>
> Use Winpayment
> GO
> SELECT S.card_acceptor_identification STORE,
> COUNT(M.card_acceptor_identification) TOTAL,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
> FROM Store S
> Left Join financial_message M
> On M.card_acceptor_identification = S.card_acceptor_identification
> And settlement_batch_number = '961'
> AND id_number_1 like '540168%'
> Where len (S.card_acceptor_identification) = 4
> GROUP BY S.card_acceptor_identification