1. Query 1:
SELECT IN_SRC_SYSTEM, COUNT(1)
FROM (SELECT
(CASE WHEN VALUE LIKE '%LO%' THEN 'LO'
WHEN VALUE LIKE '%LA%' THEN 'LA'
WHEN VALUE LIKE '%SAP%' THEN 'SAP'
WHEN VALUE LIKE '%SPA%' THEN 'SPA' ELSE 'OTHER'
END) IN_SRC_SYSTEM
FROM LOGTD
WHERE TRUNC(DATE_AGS)= TRUNC(SYSDATE)-1
AND LOG_STATUS='P'
AND VALUE IS NOT NULL)
GROUP BY IN_SRC_SYSTEM
ORDER BY 1;
output:
IN_SRC_SYSTEM count(*)
LA 62140
LO 59611
SAP 2685
SPA 95
I changed above query to return results in ROW wise.
Question: - how to add a total column in below sql. ( I can achive in above sql by group by ROllup)
SELECT
SUM(CASE WHEN VALUE LIKE '%LO%' THEN 1 ELSE 0 END) "LO" ,
SUM(CASE WHEN VALUE LIKE '%LA%' THEN 1 ELSE 0 END) "LA" ,
SUM(CASE WHEN VALUE LIKE '%SAP%' THEN 1 ELSE 0 END) "SAP",
SUM(CASE WHEN VALUE LIKE '%SPA%' THEN 1 ELSE 0 END) "SPA"
FROM LOGTD
WHERE TRUNC(DATE_AGS)= TRUNC(SYSDATE)-1
AND LOG_STATUS='P'
AND VALUE IS NOT NULL
output
LA LO SAP SPA
62140 59611 2685 95
2.
SELECT ERR_SRC_SYSTEM, COUNT( VALUE)
FROM (SELECT DISTINCT VALUE,
( CASE WHEN VALUE LIKE '%LO%' THEN 'LO'
WHEN VALUE LIKE '%LA%' THEN 'LA'
WHEN VALUE LIKE '%SAP%' THEN 'SAP'
WHEN VALUE LIKE '%SPA%' THEN 'SPA' ELSE 'OTHER'
END) ERR_SRC_SYSTEM
FROM LOGTD
WHERE TRUNC(DATEAGS)= TRUNC(SYSDATE)-1
AND LOG_STATUS='E'
AND VALUE IS NOT NULL)
GROUP BY ERR_SRC_SYSTEM
ORDER BY 1;
ERR_SRC_SYSTEM, COUNT( VALUE)
LA 174
LO 3
SPA 63
Question: - how to display columns for below case distinct values and condition ( I can achive in above sql by group by ROllup)
Expected:
LA LO SPA SAP
174 3 63 0
I tried something below , but It didnt work as it result distinct value for the column name
SELECT
SUM(DISTINCT CASE WHEN TRANSACTION_SID LIKE '%LO%' THEN 1 ELSE 0 END) "LO" ,
SUM(DISTINCT CASE WHEN TRANSACTION_SID LIKE '%LA%' THEN 1 ELSE 0 END) "LA" ,
SUM(DISTINCT CASE WHEN TRANSACTION_SID LIKE '%SAP%' THEN 1 ELSE 0 END) "SAP",
SUM(distinct CASE WHEN TRANSACTION_SID LIKE '%SPA%' THEN 1 ELSE 0 END) "SPA"
FROM OD_LG_TRANSACTION_LOG
WHERE TRUNC(CREATED_DATE)= TRUNC(SYSDATE)-1
AND LOG_STATUS='E'
AND TRANSACTION_SID IS NOT NULL
Regards,
Veera