[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle - Query to count NO value per column

Posted on 2013-01-12
5
Medium Priority
?
641 Views
Last Modified: 2013-01-12
Hello all

I need to now add 3 more column to now count the number of NO per column for these 3 columns:
SCNALIAS_SET
FREIGHT_PAY
VENDOR_RATE_SET

The 3 new counts would be at the end with column name
TOTAL_SCNALIAS_NO_SET
TOTAL_FREIGHT_PAY_NO_SET
TOTAL_VENDOR_RATE_SET_NO_SET

How can i do that?

Thanks

SELECT ID,
       SCNALIAS,
       SCNALIAS_SET,
       FREIGHT_PAY_MATCH,
       VENDOR_RATE_SET,
         DECODE(SCNALIAS_SET, 'NO', 1, 0)
       + DECODE(FREIGHT_PAY_MATCH, 'NO', 1, 0)
       + DECODE(VENDOR_RATE_SET, 'NO', 1, 0)
           ISSUES  
  FROM (SELECT DISTINCT
               LOCATION.ID,
               LOCATION.SCNALIAS,
               CASE WHEN LOCATION.SCNALIAS IS NOT NULL THEN '-' ELSE 'NO' END SCNALIAS_SET,
               NVL(
                   (SELECT '-'
                      FROM TMMGR.FP_VENDOR_INFO
                     WHERE LOCATION.SCNALIAS = FP_VENDOR_INFO.VENDOR_NBR AND ROWNUM = 1),
                   'NO'
               )
                   FREIGHT_PAY_MATCH,
               NVL(
                   (SELECT '-'
                      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 = 'CUSTOMER'
                       AND LRN.FROM_KEY_NUM = 6
                       AND LRN.FROM_LOW_KEY_VALUE(+) = LOCATION.ID
                       AND TLR.EXPIRATION > TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
                       AND ROWNUM = 1),
                   'NO'
               )
                   VENDOR_RATE_SET
          FROM TMMGR.LOCATION
         WHERE (SUBSTR(LOCATION.ID, 6, 1) BETWEEN 'A' AND ('Z')
            AND SUBSTR(LOCATION.ID, 3, 1) BETWEEN '0' AND ('9')))
ORDER BY ISSUES DESC;

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
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38770607
Can you provide an example of what you're expecting those 3 new columns to look like?
Something like you did in your previous question.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38770615
Sure.

This is what would be great to have.

Thanks again

grand_total
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38770649
how about this?  the totals will be repeated for every row


SELECT id,
       scnalias,
       scnalias_set,
       freight_pay_match,
       vendor_rate_set,
         DECODE(scnalias_set, 'NO', 1, 0)
       + DECODE(freight_pay_match, 'NO', 1, 0)
       + DECODE(vendor_rate_set, 'NO', 1, 0)
           issues,
       COUNT(DECODE(scnalias_set, 'NO', 1)) OVER () total_scnalias_no_set,
       COUNT(DECODE(freight_pay_match, 'NO', 1)) OVER () total_freight_pay_no_set,
       COUNT(DECODE(vendor_rate_set, 'NO', 1)) OVER () total_vendor_rate_set_no_set
  FROM (SELECT DISTINCT
               location.id,
               location.scnalias,
               CASE WHEN location.scnalias IS NOT NULL THEN '-' ELSE 'NO' END scnalias_set,
               NVL(
                   (SELECT '-'
                      FROM tmmgr.fp_vendor_info
                     WHERE location.scnalias = fp_vendor_info.vendor_nbr AND ROWNUM = 1),
                   'NO'
               )
                   freight_pay_match,
               NVL(
                   (SELECT '-'
                      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 = 'CUSTOMER'
                       AND lrn.from_key_num = 6
                       AND lrn.from_low_key_value(+) = location.id
                       AND tlr.expiration > TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
                       AND ROWNUM = 1),
                   'NO'
               )
                   vendor_rate_set
          FROM tmmgr.location
         WHERE (SUBSTR(location.id, 6, 1) BETWEEN 'A' AND ('Z')
            AND SUBSTR(location.id, 3, 1) BETWEEN '0' AND ('9')))
ORDER BY issues DESC;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38770652
if you want to force the counts to only be on the first row, try this...


SELECT id,
       scnalias,
       scnalias_set,
       freight_pay_match,
       vendor_rate_set,
       issues,
       DECODE(ROWNUM, 1, total_scnalias_no_set) total_scnalias_no_set,
       DECODE(ROWNUM, 1, total_freight_pay_no_set) total_freight_pay_no_set,
       DECODE(ROWNUM, 1, total_vendor_rate_set_no_set) total_vendor_rate_set_no_set
  FROM (SELECT id,
               scnalias,
               scnalias_set,
               freight_pay_match,
               vendor_rate_set,
                 DECODE(scnalias_set, 'NO', 1, 0)
               + DECODE(freight_pay_match, 'NO', 1, 0)
               + DECODE(vendor_rate_set, 'NO', 1, 0)
                   issues,
               COUNT(DECODE(scnalias_set, 'NO', 1)) OVER () total_scnalias_no_set,
               COUNT(DECODE(freight_pay_match, 'NO', 1)) OVER () total_freight_pay_no_set,
               COUNT(DECODE(vendor_rate_set, 'NO', 1)) OVER () total_vendor_rate_set_no_set
          FROM (SELECT DISTINCT
                       location.id,
                       location.scnalias,
                       CASE WHEN location.scnalias IS NOT NULL THEN '-' ELSE 'NO' END scnalias_set,
                       NVL(
                           (SELECT '-'
                              FROM tmmgr.fp_vendor_info
                             WHERE location.scnalias = fp_vendor_info.vendor_nbr AND ROWNUM = 1),
                           'NO'
                       )
                           freight_pay_match,
                       NVL(
                           (SELECT '-'
                              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 = 'CUSTOMER'
                               AND lrn.from_key_num = 6
                               AND lrn.from_low_key_value(+) = location.id
                               AND tlr.expiration >
                                       TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
                               AND ROWNUM = 1),
                           'NO'
                       )
                           vendor_rate_set
                  FROM tmmgr.location
                 WHERE (SUBSTR(location.id, 6, 1) BETWEEN 'A' AND ('Z')
                    AND SUBSTR(location.id, 3, 1) BETWEEN '0' AND ('9')))
        ORDER BY issues DESC);
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38770658
wow,

this is perfect

thanks you so mutch again for your help
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

649 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