Solved

Oracle - Count Distinct in UNION query

Posted on 2014-02-10
6
1,090 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
  • 4
  • 2
6 Comments
 
LVL 76

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 76

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 76

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

758 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

22 Experts available now in Live!

Get 1:1 Help Now