asked on

Oracle - Count with 3 criteria

Hi all

I am trying to build the SQL that would count the number of time i have a match on those 3 crirerias below:
1-TLR.CARRIER_ID,
2-LRN.FROM_LOW_KEY_VALUE,
3-LRN.TO_LOW_KEY_VALUE

How can i do that?

Thanks again for your help

SELECT TLR.CARRIER_ID, LRN.FROM_LOW_KEY_VALUE,LRN.TO_LOW_KEY_VALUE
FROM 
    TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
WHERE 
    TLR.CARRIER_ID = TRD.CARRIER_ID (+)
    AND TLR.LANE_ID = TRD.LANE_ID (+)
    AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID (+)
    AND TLR.EFFECTIVE = TRD.EFFECTIVE (+)
    AND TLR.LANE_ID = LRN.ID
    AND TLR.CARRIER_ID = 'BULK'
    AND LRN.FROM_LOW_KEY_VALUE = 'ROBERTXD'
    AND LRN.TO_LOW_KEY_VALUE = '24'
    AND TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')-365

Open in new window

slightwv (䄆 Netminder)

Match against what?

Can you provide some sample data and expected results?

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Hi all

let me try that.

I was just testing like this also it it looks like i have the good result so far:

SELECT COUNT (TLR.CARRIER_ID)
FROM 
    TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
WHERE 
    TLR.CARRIER_ID = TRD.CARRIER_ID (+)
    AND TLR.LANE_ID = TRD.LANE_ID (+)
    AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID (+)
    AND TLR.EFFECTIVE = TRD.EFFECTIVE (+)
    AND TLR.LANE_ID = LRN.ID
    AND TLR.CARRIER_ID = 'BULK'
    AND LRN.FROM_LOW_KEY_VALUE = 'ROBERTXD'
    AND LRN.TO_LOW_KEY_VALUE = '24'
    AND TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')-365
    GROUP by TLR.CARRIER_ID, LRN.FROM_LOW_KEY_VALUE,LRN.TO_LOW_KEY_VALUE

Open in new window

slightwv (䄆 Netminder)

A count of one is a count for all unless you have some null values.

Are you expecting different numbers?

Please post sample data from your query and your expected results.

PortletPaul

those 3 fields will produce the same count as all 3 of them are used in the where clause

SELECT 
      count(TLR.CARRIER_ID) 
    , count(LRN.FROM_LOW_KEY_VALUE)
    , count(LRN.TO_LOW_KEY_VALUE)
FROM 
    TL_RATE TLR
    LEFT JOIN TL_RATE_DETAIL TRD ON TLR.CARRIER_ID = TRD.CARRIER_ID
                                AND TLR.LANE_ID = TRD.LANE_ID
                                AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID
                                AND TLR.EFFECTIVE = TRD.EFFECTIVE
    INNER JOIN LANE_RATE_NETWORK LRN ON TLR.LANE_ID = LRN.ID
WHERE   TLR.CARRIER_ID = 'BULK'
    AND LRN.FROM_LOW_KEY_VALUE = 'ROBERTXD'
    AND LRN.TO_LOW_KEY_VALUE = '24'
    AND TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')-365

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Hi all thanks this is now working great