ADDING WITH COUNTING

16 Sep 2005 6:04 PM

tarheels4025

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

16 Sep 2005 6:46 PM

Brian Lawton

I'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.)

Show quote

"tarheels4025" <tarheels4***@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