Wilder1626
asked on
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_S ET
How can i do that?
Thanks
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_S
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;
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_m atch, 'NO', 1)) OVER () total_freight_pay_no_set,
COUNT(DECODE(vendor_rate_s et, 'NO', 1)) OVER () total_vendor_rate_set_no_s et
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;
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,
COUNT(DECODE(freight_pay_m
COUNT(DECODE(vendor_rate_s
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow,
this is perfect
thanks you so mutch again for your help
this is perfect
thanks you so mutch again for your help
Something like you did in your previous question.