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

LVL 11
Wilder1626Asked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
Match against what?

Can you provide some sample data and expected results?
0
SharathData EngineerCommented:
Do not understand completely. Are you looking for this?
SELECT COUNT(*)
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

If not post the sample result of your query and the expected result.
0
slightwv (䄆 Netminder) Commented:
If I had to guess, you want to count when each of those fields 'match' something.

If so, a SUM and CASE statement will work:

SELECT sum(case when TLR.CARRIER_ID = 'somevalue' then 1 end),
sum(case(LRN.FROM_LOW_KEY_VALUE = 'someothervalue' then 1 end)
sum(case(LRN.TO_LOW_KEY_VALUE = 'bob' then 1 end)
from ...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wilder1626Author Commented:
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

0
slightwv (䄆 Netminder) Commented:
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.
0
PortletPaulfreelancerCommented:
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

0
PortletPaulfreelancerCommented:
btw, instead of this
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')-365

try
TRUNC(SYSDATE) - 365
0

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
Wilder1626Author Commented:
Hi all thanks this is now working great
0
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
Oracle Database

From novice to tech pro — start learning today.