Hi, I need a SQL query as below..

select b.cust_id,b.cust_nm,count(a.STATUS_CD),
from Table a, table b
where updt_ts between TO_DATE('22-FEB-2018 00:00:00','DD-MON-YYYY HH24:MI:SS')
And To_Date('28-FEB-2018 23:59:59','DD-MON-YYYY HH24:MI:SS') and a.cust_id=b.cust_id
group by b.cust_id,b.cust_nm,a.status_cd;

I need to add a column which will filter how many 'pending' transaction from STATUS_CD column and add that column next to the count(status_cd). Please share your thoughts.

venkatesh SarivisettySenior Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
you can use a SUM;
SUM(case when status_CD when 'pending' then 1 else 0) end as CountPending

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
1) Use table alias names for all tables and columns. E.g. where belongs updt_ts to?
2) Use the new ANSI SQL JOIN syntax. It's hard to read otherwise.
3) Take a look at COUNT(*) vs. COUNT(DISTINCT a.STATUS_CD) and SUM(CASE WHEN a.STATUS_CD = 'Pending' THEN 1 ELSE 0 END).
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Hi Eric, Thanks for your reply Now I have designed a query like this , but what is the issue is I am customer id and customer name repeatedly but in that only one row is correct , not sure from where the other rows are getting .. pls clarify and let me know if you need any more info

select distinct b.cust_id,b.cust_nm,count(a.STATUS_CD) as Total_Count, sum(decode(a.status_cd,'pending',1,0)) as Pending,
 sum(decode(a.status_cd,'Error',1,0)) as Error,sum(decode(a.status_cd,'success',1,0)) as success,
 sum(decode(a.status_cd,'existing',1,0)) as Existing,
 sum(decode(a.status_cd,'canceled',1,0)) as Cancelled,
 sum(decode(a.status_cd,'(null)',1,0)) as Null_Values
 from table a, table b
 where a.updt_ts between TO_DATE('01-JAN-2018 00:00:00','DD-MON-YYYY HH24:MI:SS')
 And To_Date('28-FEB-2018 23:59:59 ','DD-MON-YYYY HH24:MI:SS') and b.cust_id=a.cust_id
 group by b.cust_id,b.cust_nm,a.status_cd,decode(a.STATUS_CD,'pending',1,0)
 order by b.cust_id,b.cust_nm;
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Éric MoreauSenior .Net ConsultantCommented:
Not sure to understand what you mean! what is "decode"?

can you generate a query using an in memory (table declare @test table) with some data and show us the expected results from that data?
ste5anSenior DeveloperCommented:
Why do you assume that (b.cust_id,b.cust_nm,a.status_cd,decode(a.STATUS_CD,'pending',1,0) ) should return only one row per cust_id?
venkatesh SarivisettySenior Software EngineerAuthor Commented:
In Oracle, you can use DECODE function to evaluate a list of expressions, and if a value is matched return the corresponding result.
Éric MoreauSenior .Net ConsultantCommented:
Oh! I am not used to Oracle, only MS SQL Server. That wasn't clear from the question or the selected topics.
venkatesh SarivisettySenior Software EngineerAuthor Commented:
oK nO problem thanks
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Is there any way to provide sysdate to automatically for

past 1 week and past 8 hours instead of giving manually into the script
awking00Information Technology SpecialistCommented:
It would be of great help if you could provide some sample data (with the table structures) and some expected results. From what you've shown so far, I see a couple of possible issues. One, you show the join as being between table aliased as a and table aliased as b but I assume you may mean tableA as a and tableB as b. You also show count(STATUS_CD) as total_count but I think you need to show count(*) as total_count since, if there are null values in STATUS_CD, they will not be counted. Along that same line, you show sum(decode(STATUS_CD,'(null)',1,0)) as Null_Values. I'm not sure that the quotes and parentheses are needed (or even acceptable) to compare the STATUS_CD with null. Also, you should need to use the distinct keyword with the aggregates.
venkatesh SarivisettySenior Software EngineerAuthor Commented:
For example, If I have a column consists of a json like the complete xml printed in it, How I can use this column for matching

For example, a Json column having a value like below and other column having the customer id 12345678, how I can match these 2 values like a.cust_id = b.cust_id??? is there any syntax for json values. Please help me

Json value - {"customer_id":"12345678","FEW DATA"};
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.