Calculating totals

Hello. I am looking to write a query that will allow me to count all
pos_entry_code = 921 at all stores (card_acceptor_identification). Then hav
e
a total for each store. Is this possible? This is what I have so far. Do
I
need a group by and is there all way to list all the stores just not stores
with greater than 0. Thanks for any help.
Use WinPayment
GO
SELECT settlement_batch_number, card_acceptor_identification, id_number_1,
transaction_amount, id_code_1, host_response_string, count(*) count
FROM financial_message
where settlement_batch_number = '843'
and
pos_entry_mode = '921'
GROUP BY settlement_batch_number, card_acceptor_identification, id_number_1,
transaction_amount, id_code_1, host_response_string
HAVING COUNT(*) > 1
order by
card_acceptor_identificationThis is better if you do it in your client app / reporting tool. Take a look
to operator ROLLUP in BOL.
AMB
"tarheels4025" wrote:

> Hello. I am looking to write a query that will allow me to count all
> pos_entry_code = 921 at all stores (card_acceptor_identification). Then h
ave
> a total for each store. Is this possible? This is what I have so far. D
o I
> need a group by and is there all way to list all the stores just not store
s
> with greater than 0. Thanks for any help.
> Use WinPayment
> GO
> SELECT settlement_batch_number, card_acceptor_identification, id_number_1,
> transaction_amount, id_code_1, host_response_string, count(*) count
> FROM financial_message
> where settlement_batch_number = '843'
> and
> pos_entry_mode = '921'
> GROUP BY settlement_batch_number, card_acceptor_identification, id_number_
1,
> transaction_amount, id_code_1, host_response_string
> HAVING COUNT(*) > 1
> order by
> card_acceptor_identification|||So there is no way to easily sum numbers in SQL?
"Alejandro Mesa" wrote:
> This is better if you do it in your client app / reporting tool. Take a lo
ok
> to operator ROLLUP in BOL.
>
> AMB
> "tarheels4025" wrote:
>|||If you have a table with all the stores in it, use an outer join from it to
the financial_message Table...
SELECT S.card_acceptor_identification,
settlement_batch_number,
id_number_1, transaction_amount,
id_code_1, host_response_string,
count(*) count
FROM StoresTable S
Left Join financial_message M
On M.card_acceptor_identification =
S.card_acceptor_identification
Where settlement_batch_number = '843'
and pos_entry_mode = '921'
GROUP BY S.card_acceptor_identification,
settlement_batch_number,
id_number_1,transaction_amount,
id_code_1, host_response_string
HAVING COUNT(*) > 1
Order By card_acceptor_identification
"tarheels4025" wrote:

> Hello. I am looking to write a query that will allow me to count all
> pos_entry_code = 921 at all stores (card_acceptor_identification). Then h
ave
> a total for each store. Is this possible? This is what I have so far. D
o I
> need a group by and is there all way to list all the stores just not store
s
> with greater than 0. Thanks for any help.
> Use WinPayment
> GO
> SELECT settlement_batch_number, card_acceptor_identification, id_number_1,
> transaction_amount, id_code_1, host_response_string, count(*) count
> FROM financial_message
> where settlement_batch_number = '843'
> and
> pos_entry_mode = '921'
> GROUP BY settlement_batch_number, card_acceptor_identification, id_number_
1,
> transaction_amount, id_code_1, host_response_string
> HAVING COUNT(*) > 1
> order by
> card_acceptor_identification|||I am receiving an error that says Ambiguous column name
'Card_Acceptor_Identification'. What does that mean? Thanks.
"CBretana" wrote:
> If you have a table with all the stores in it, use an outer join from it t
o
> the financial_message Table...
> SELECT S.card_acceptor_identification,
> settlement_batch_number,
> id_number_1, transaction_amount,
> id_code_1, host_response_string,
> count(*) count
> FROM StoresTable S
> Left Join financial_message M
> On M.card_acceptor_identification =
> S.card_acceptor_identification
> Where settlement_batch_number = '843'
> and pos_entry_mode = '921'
> GROUP BY S.card_acceptor_identification,
> settlement_batch_number,
> id_number_1,transaction_amount,
> id_code_1, host_response_string
> HAVING COUNT(*) > 1
> Order By card_acceptor_identification
> "tarheels4025" wrote:
>|||That that Column name is in more than one of the tables referenced in your
query, so you have to "Qualify" it where you used it with a prefix, which
specifies which table you want it to come from... Thius is my bad, I missed
it. Notice the "S." in the first line? Well, I should have done the sam
e
thing where I used the column name in the Order By as well. That tells the
Query Processor to Order By The value of card_acceptor_identification in the
"S" Table (S is the alias assigned to the StoresTable by the syntax "Stores
As S" (Actually I left out the word "As" Since it's optional)
ANyway, below is the changed version:
Again, replace the "StoresTable" with whatever your table is actually named.
.
SELECT S.card_acceptor_identification,
settlement_batch_number,
id_number_1, transaction_amount,
id_code_1, host_response_string,
count(*) count
FROM StoresTable S
Left Join financial_message M
On M.card_acceptor_identification =
S.card_acceptor_identification
Where settlement_batch_number = '843'
and pos_entry_mode = '921'
GROUP BY S.card_acceptor_identification,
settlement_batch_number,
id_number_1,transaction_amount,
id_code_1, host_response_string
HAVING COUNT(*) > 1
Order By S.card_acceptor_identification
"tarheels4025" wrote:
> I am receiving an error that says Ambiguous column name
> 'Card_Acceptor_Identification'. What does that mean? Thanks.
> "CBretana" wrote:
>|||That worked thank. Is there a way to show every store and the totals from
each store. Maybe show every store from store table and then a column that
says count. Thanks
"CBretana" wrote:
> That that Column name is in more than one of the tables referenced in your
> query, so you have to "Qualify" it where you used it with a prefix, which
> specifies which table you want it to come from... Thius is my bad, I miss
ed
> it. Notice the "S." in the first line? Well, I should have done the s
ame
> thing where I used the column name in the Order By as well. That tells t
he
> Query Processor to Order By The value of card_acceptor_identification in t
he
> "S" Table (S is the alias assigned to the StoresTable by the syntax "Stor
es
> As S" (Actually I left out the word "As" Since it's optional)
> ANyway, below is the changed version:
> Again, replace the "StoresTable" with whatever your table is actually name
d...
> SELECT S.card_acceptor_identification,
> settlement_batch_number,
> id_number_1, transaction_amount,
> id_code_1, host_response_string,
> count(*) count
> FROM StoresTable S
> Left Join financial_message M
> On M.card_acceptor_identification =
> S.card_acceptor_identification
> Where settlement_batch_number = '843'
> and pos_entry_mode = '921'
> GROUP BY S.card_acceptor_identification,
> settlement_batch_number,
> id_number_1,transaction_amount,
> id_code_1, host_response_string
> HAVING COUNT(*) > 1
> Order By S.card_acceptor_identification
>
> "tarheels4025" wrote:
>|||tarheels4025,
I'm not sure what you are now asking...
The last query I posted, if i tworked SHOULD be showing every store, and a
column that says Count...
Could you be more specific?
"tarheels4025" wrote:
> That worked thank. Is there a way to show every store and the totals from
> each store. Maybe show every store from store table and then a column tha
t
> says count. Thanks
> "CBretana" wrote:
>|||Well it is only showing the stores that have more than one instance of a
pos_entry_code of 921. I would like all stores to be shown and show the
count of each store of instances of 921. The count will be a total for each
store. I hope that explains it. Thanks.
"CBretana" wrote:
> tarheels4025,
> I'm not sure what you are now asking...
> The last query I posted, if i tworked SHOULD be showing every store, and
a
> column that says Count...
> Could you be more specific?
> "tarheels4025" wrote:
>|||Might it have something to do with having count(*)>1?
thx
md
*** Sent via Developersdex http://www.examnotes.net ***