SQL Query to pull the transactions based on the status

I have a table which consists of list of transactions, I need to get the output as below

Customer name         Status            Count of Status
ABC Bank                    Success                    10
ABC Bank                    Failures                      5
XYZ Bank                     Success                      5

Can someone provide me the query ? and let me know if you need any more details.

Thanks
Venkatesh.
venkatesh SarivisettySenior Software EngineerAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
now you describe some columns with text, but not the actual column names
besides, you're missing a few columns like status_cd

in sql*plus you can use the descr TABLE_NAME to describe a table
and then you can copy/paste that output

the trunc might cause performance problems
a.updt_ts >= to_date('25-FEB-18','DD-MON-YY') AND to_date('25-FEB-18','DD-MON-YY')
and it will only work if the nls_language is in English, you're making an assumption that this will always be the case
this is what happens with assumptions:
https://www.youtube.com/watch?v=wg4trPZFUwc

as a senior software engineer you have probably experienced that correct details are a must to get something working correctly
0
 
slightwv (䄆 Netminder) Commented:
select customer_name, status, count(*)
from transaction_table
group by customer_name, status;

Open in new window

If that isn't it, please provide some raw sample data to go with your expected results.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Hi, thanks for your reply.

Actually I have Customer name in one table and consumer transaction details in one table(which is having the customer id field also
)

As you said

select customer_name, status, count(*)
from transaction_table
group by customer_name, status;

Customer name I need to pull from one table and status from one table

Table A - Customer name
Table B - Consumer transaction details.

Need to join these 2 tables based on one key called digital and pull how many transactionssuccess/ failure count and Login Success/failure count from a consumer perspective.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
I have written this script

select C.CUST_NM,A.FIRST_NM,A.LAST_NM,A.MIDDLE_NM,B.STATUS_CD
from table a, table b, table c
WHERE b.TRANS_TYPE_TXT = 'Login' and
c.cust_id = a.cust_id
AND TRUNC(a.updt_ts) BETWEEN to_date('25-FEB-18 00:00:00','DD-MON-YY HH24:MI:SS') AND to_date('25-FEB-18 23:59:59','DD-MON-YY HH24:MI:SS')
group by c.cust_nm, b.STATUS_CD,a.FIRST_NM,a.LAST_NM,a.MIDDLE_NM
order by c.cust_nm, b.STATUS_CD;

I got the result as below, I need to show a column at the end like count of Completed, Error and Pending transaction under each customer or consumer. How I can accomplish that?

Bank                                                              DAVID      CANOVA                                    Completed
Bank                                                              LARRY      ANDERSON-CAPPS            Completed
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Joining two tables is really simple and there are MANY examples on the web.

Using my first example:
select customer_name, status, count(*)
from transaction_table t join customer c on t.customer_id=c.customer_id
group by customer_name, status;

Open in new window


>>I have written this script

You have a group by but you don't include the COUNT(*).

In your example, you included table_b but don't join it to the other tables.

For every table in your FROM clause, you need to join it to other columns in your WHERE clause.

That is why I like the ANSI join syntax over the syntax you used.  It makes the joins more obvious.

You still didn't post raw data so I cannot provide tested code.  I can only guess.

You will need to add the join columsn for table_b but here is a start:
select C.CUST_NM,A.FIRST_NM,A.LAST_NM,A.MIDDLE_NM,B.STATUS_CD, count(*) status_count
from table_a a
      join  table_b b on a.SOME_COLUMN=b.SOME_OTHER_COLUMN
      join table_c c on c.cust_id = a.cust_id
WHERE b.TRANS_TYPE_TXT = 'Login'
     AND TRUNC(a.updt_ts) BETWEEN to_date('25-FEB-18 00:00:00','DD-MON-YY HH24:MI:SS') AND to_date('25-FEB-18 23:59:59','DD-MON-YY HH24:MI:SS')
group by c.cust_nm, b.STATUS_CD,a.FIRST_NM,a.LAST_NM,a.MIDDLE_NM
order by c.cust_nm, b.STATUS_CD;

You need to fill in the table_b column joins.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Actually I am running against the production database, so database will have more transaction. My requirement here is to get the complete success/failure/pending transactions based on the customer & consumer details from below details.

Table A - Transaction ID , Digital ID, Consumer first name, Consumer last name, Consumer middle name
Table B - Digital ID, Transaction type text
Table C - Customer Name and Customer ID

Need to use these fields from table and pull the transaction success/failure counts from database. Output will have only Customer name and consumer names and status of the transaction and count of the failures.
0
 
johnsoneSenior Oracle DBACommented:
I find it amazing that the original question was one table and two columns.  Now, it is up to three tables and nine columns.
0
 
slightwv (䄆 Netminder) Commented:
>>My requirement here is to get the complete success/failure/pending transactions based on the customer & consumer details from below details

I understand your requirement.  I have posted conceptually what you need.  You should be able to take that and tweak it.  You know your tables better than we do.

What parts of what I posted are you not understanding?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.