Oracle - Count Distinct in UNION query

Hi all

I have this below query that count the result between 2 queries in UNION.

But i have a count for the first part of the query and a second count in the second part of the query. i would like to have a total count from both queries.

Ex:

Now i see:
USED      CARRIER_ID      ORIGIN      DESTINATION
1               A&A_03            N3H 4R7      ZONE_01
51               A&A_03            N3H 4R7      ZONE_01

But i would like to see:
USED      CARRIER_ID      ORIGIN      DESTINATION
52               A&A_03            N3H 4R7      ZONE_01


How can i do that

Thanks

SELECT   DISTINCT COUNT (DISTINCT BILL) AS USED,
                    LOAD.CARRIER_ID,
                    R.FROM_LOW_KEY_VALUE AS ORIGIN,
                    R.TO_LOW_KEY_VALUE AS DESTINATION,
                    TR.TARIFF_CLASS_ID,
                    TO_CHAR (TR.EFFECTIVE, 'yyyymmdd'),
                    TO_CHAR (TR.DATE_INVALID, 'yyyymmdd'),
                    TR.LANE_ID
    FROM   LOAD, TL_RATE TR, LANE_RATE_NETWORK R
   WHERE       BEST_DEP > TO_DATE ('20090101', 'YYYYMMDD')
           AND LOAD.RATE_KEY = TR.RATE_KEY
           AND TR.LANE_ID = R.ID
           AND LOAD.CARRIER_ID NOT IN
                    ('CLEANUP',
                     'CUSTOMER',
                     'SPOT_RATE',
                     'SPOT_RATE_IM',
                     'CALPROF-PER',
                     'CALPROF-GROC')
           AND TR.DATE_INVALID >
                 TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
GROUP BY   LOAD.RATE_KEY,
           R.FROM_LOW_KEY_VALUE,
           R.FROM_HIGH_KEY_VALUE,
           R.FROM_COUNTRY,
           R.TO_LOW_KEY_VALUE,
           R.TO_HIGH_KEY_VALUE,
           R.TO_COUNTRY,
           LOAD.CARRIER_ID,
           TR.EFFECTIVE,
           TR.DATE_INVALID,
           TR.TARIFF_CLASS_ID,
           TR.LANE_ID
UNION
  SELECT   DISTINCT COUNT (DISTINCT LOAD_ID) AS USED,
                    LOAD.MASTER_CARRIER_ID,
                    R.FROM_LOW_KEY_VALUE AS ORIGIN,
                    R.TO_LOW_KEY_VALUE AS DESTINATION,
                    TR.TARIFF_CLASS_ID,
                    TO_CHAR (TR.EFFECTIVE, 'yyyymmdd'),
                    TO_CHAR (TR.DATE_INVALID, 'yyyymmdd'),
                    TR.LANE_ID
    FROM   FP_MASTER_HIST LOAD, TL_RATE TR, LANE_RATE_NETWORK R
   WHERE   LOAD.RATE_KEY = TR.RATE_KEY AND TR.LANE_ID = R.ID
           AND LOAD.MASTER_CARRIER_ID NOT IN
                    ('CLEANUP',
                     'CUSTOMER',
                     'SPOT_RATE',
                     'SPOT_RATE_IM',
                     'CALPROF-PER',
                     'CALPROF-GROC')
           AND TR.DATE_INVALID >
                 TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
GROUP BY   LOAD.RATE_KEY,
           R.FROM_LOW_KEY_VALUE,
           R.FROM_HIGH_KEY_VALUE,
           R.FROM_COUNTRY,
           R.TO_LOW_KEY_VALUE,
           R.TO_HIGH_KEY_VALUE,
           R.TO_COUNTRY,
           LOAD.MASTER_CARRIER_ID,
           TR.EFFECTIVE,
           TR.DATE_INVALID,
           TR.TARIFF_CLASS_ID,
           TR.LANE_ID;

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:
Just wrap it in an outer select?


select sum(USED),  CARRIER_ID, ...
from
(
--your select
)
group by  CARRIER_ID, ...
0
slightwv (䄆 Netminder) Commented:
What does this do?

AND TR.DATE_INVALID > TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')

Seems this would work:
AND TR.DATE_INVALID >  trunc(SYSDATE)
0
slightwv (䄆 Netminder) Commented:
I'm also thinking you can do away with the union or at least limit the rows.

There appears to be a LOT in common between the two.

Worst case, move the common query into an inline view or a WITH statement.

Then join the rest with that.

I'm just not seeing the 'big picture' from the SQL you posted but my raw SQL isn't all that strong.  I need to play with samples.

Can you provide a simplified test case with sample data and expected results?

Maybe another Expert can see the big picture.  I'm confident what you posted can be simplified.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Wilder1626Author Commented:
Hi slightwv

I dont think i understand what you are saying by: I'm also thinking you can do away with the union or at least limit the rows.

But i did what you said in post: ID: 39848918

Looks like it work but i will validate all the data:

  SELECT   SUM (USED), CARRIER_ID,
           ORIGIN,
           DESTINATION,
           CLASSES,
           EFFECTIVE_DATE,
           INVALID_DATE,
           LANE_ID
    FROM   (  SELECT   DISTINCT
                       COUNT (DISTINCT BILL) AS USED,
                       LOAD.CARRIER_ID AS CARRIER_ID,
                       R.FROM_LOW_KEY_VALUE AS ORIGIN,
                       R.TO_LOW_KEY_VALUE AS DESTINATION,
                       TR.TARIFF_CLASS_ID AS CLASSES,
                       TO_CHAR (TR.EFFECTIVE, 'yyyymmdd') AS EFFECTIVE_DATE,
                       TO_CHAR (TR.DATE_INVALID, 'yyyymmdd') AS INVALID_DATE,
                       TR.LANE_ID AS LANE_ID
                FROM   LOAD, TL_RATE TR, LANE_RATE_NETWORK R
               WHERE       BEST_DEP > TO_DATE ('20090101', 'YYYYMMDD')
                       AND LOAD.RATE_KEY = TR.RATE_KEY
                       AND TR.LANE_ID = R.ID
                       AND LOAD.CARRIER_ID NOT IN
                                ('CLEANUP',
                                 'CUSTOMER',
                                 'SPOT_RATE',
                                 'SPOT_RATE_IM',
                                 'CALPROF-PER',
                                 'CALPROF-GROC')
                       AND TR.DATE_INVALID > TRUNC (SYSDATE)
            GROUP BY   LOAD.RATE_KEY,
                       R.FROM_LOW_KEY_VALUE,
                       R.FROM_HIGH_KEY_VALUE,
                       R.FROM_COUNTRY,
                       R.TO_LOW_KEY_VALUE,
                       R.TO_HIGH_KEY_VALUE,
                       R.TO_COUNTRY,
                       LOAD.CARRIER_ID,
                       TR.EFFECTIVE,
                       TR.DATE_INVALID,
                       TR.TARIFF_CLASS_ID,
                       TR.LANE_ID
            UNION
              SELECT   DISTINCT
                       COUNT (DISTINCT LOAD_ID) AS USED,
                       LOAD.MASTER_CARRIER_ID CARRIER_ID,
                       R.FROM_LOW_KEY_VALUE AS ORIGIN,
                       R.TO_LOW_KEY_VALUE AS DESTINATION,
                       TR.TARIFF_CLASS_ID AS CLASSES,
                       TO_CHAR (TR.EFFECTIVE, 'yyyymmdd') AS EFFECTIVE_DATE,
                       TO_CHAR (TR.DATE_INVALID, 'yyyymmdd') AS INVALID_DATE,
                       TR.LANE_ID AS LANE_ID
                FROM   FP_MASTER_HIST LOAD, TL_RATE TR, LANE_RATE_NETWORK R
               WHERE   LOAD.RATE_KEY = TR.RATE_KEY AND TR.LANE_ID = R.ID
                       AND LOAD.MASTER_CARRIER_ID NOT IN
                                ('CLEANUP',
                                 'CUSTOMER',
                                 'SPOT_RATE',
                                 'SPOT_RATE_IM',
                                 'CALPROF-PER',
                                 'CALPROF-GROC')
                       AND TR.DATE_INVALID > TRUNC (SYSDATE)
            GROUP BY   LOAD.RATE_KEY,
                       R.FROM_LOW_KEY_VALUE,
                       R.FROM_HIGH_KEY_VALUE,
                       R.FROM_COUNTRY,
                       R.TO_LOW_KEY_VALUE,
                       R.TO_HIGH_KEY_VALUE,
                       R.TO_COUNTRY,
                       LOAD.MASTER_CARRIER_ID,
                       TR.EFFECTIVE,
                       TR.DATE_INVALID,
                       TR.TARIFF_CLASS_ID,
                       TR.LANE_ID)
GROUP BY   CARRIER_ID,
           ORIGIN,
           DESTINATION,
           CLASSES,
           EFFECTIVE_DATE,
           INVALID_DATE,
           LANE_ID;

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>I dont think i understand what you are saying by: I'm also thinking you can do away with the union or at least limit the rows.

It looks like 90% of the two queries are the same.  When I see things like that I think there has to be a way to combine that 90% and only access the tables involved once.

You should be able to join the common tables/columns in an inline view then select the 'distinct' stuff with that but I would need sample data and expected results to confirm.

Something like (just typed in so syntax could be way off):
with mydata as (
select comon1, common2, common3
from common_table1 c1, common_table2 c2
where c1.col1 = c2.col1
)
select col1, count(fred) from other_tab1 o1, mydata  md where o1.col1=md.common1
union
select col1, count(barney) from other_tab2 o2, mydata  md where o2.col1=md.common1
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:
Perfect thanks for your help. Now i'm all good to go.
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.

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.