Solved

Oracle - Count Distinct in UNION query

Posted on 2014-02-10
6
1,121 Views
Last Modified: 2014-02-12
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

0
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39848918
Just wrap it in an outer select?


select sum(USED),  CARRIER_ID, ...
from
(
--your select
)
group by  CARRIER_ID, ...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39848919
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39848927
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 11

Author Comment

by:Wilder1626
ID: 39848986
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39849071
>>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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39853979
Perfect thanks for your help. Now i'm all good to go.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Developer - SubString 2 48
SQL Server how to create a DYNAMIC TABLE? 11 45
T-SQL: Wrong Result 7 34
SQL - Aging Report - Display Months with no data 8 40
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

738 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