Posted on 2011-04-19
Last Modified: 2012-05-11
I have table T1 with a production stages .


The values allowed are (A1, B1, C1, D1, E1).

I have a query that counts the records based on those stages.

However, A1 and B1 are for same Title.

Is it possible to count either one of those or boths as one record

So if if i have A1 it will be counted 1
if i have B1 it will be counted 1
if i have both A1 and B1 they will be coutned 1.

would you create a derived column and sum on that.
Question by:sam15
    LVL 73

    Expert Comment

    I'm not sure what you're looking for, please provide sample data and expected output
    LVL 31

    Expert Comment

    Are you saying that, in addition to the pr_stage column, there is also a Title column and, if a given title has pr_stage of only A1, it should be counted as 1, or has a value of only B1, it should be counted as 1, and if it has both an A1 and B1 values, it should still only be counted as 1?

    Author Comment

    yes, there is another column. I am just trying to explain it in simple terms.

    create table t1 (
    pr_stage varchar2(2)

    insert into t1 values ('A1')
    insert into t1 values ('B1')
    insert into t1 values ('C1')
    insert into t1 values ('D1')

    Select count(*) from t1 ---> should be 3 beause A1 and B1 point to same titlle.
    LVL 73

    Accepted Solution

    what if A1 exists twice?  or D1?

    if you want a distinct count try...

    Select count(distinct decode(pr_stage,'B1','A1',pr_stage)) from t1

    Author Comment

    yes that should do it

    replace the value of B1 with A1 and do a DISTINCT COUNT.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now