[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Oracle - Query to count NO value per column

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
Wilder1626
Asked:
Wilder1626
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
Sure.

This is what would be great to have.

Thanks again

grand_total
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
wow,

this is perfect

thanks you so mutch again for your help
0

Featured Post

Technology Partners: 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!

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