[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1193
  • Last Modified:

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

0
Wilder1626
Asked:
Wilder1626
  • 4
  • 2
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
Wilder1626Author Commented:
Perfect thanks for your help. Now i'm all good to go.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now